<?php
/**
 * dbfront
 * (c) 2010-2013 Stephen Adkins <spadkins@gmail.com>
 * This software began development in 2010.
 * It is based on code dating from 1999.
 *
 * License: GPL 2.0
 * The contents of this file may be used under the terms of
 * the GNU General Public License Version 2 or later (the "GPL").
 *
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 * */
namespace App;

define('META_DB_NAME', 'meta');

class Database extends Service {
    public    $db_id, $db_name, $db_connection_id; # attributes from the "db" table that become public attributes of this object
    public    $owner_group_id, $db_env, $db_owner_group_id;
    protected $dbtype, $dbhost, $schema_name, $dsn, $dbuser, $dbpass, $dboptions;   # attributes from the "db" table that become attributes of this object
    protected $dboption;        # this is the parsed array of dboptions
    protected $schema_defs;     # rows from the "db_schema" table for this database (db_schema.db_id = $this->db_id)
    protected $metadb;          # the metadata database for this database
    protected $case_sensitive = 0, $func_strtoupper, $func_strtolower;
    protected $table_defs;      # rows from the "db_table" table for each schema in this database (loaded only as necessary)
    protected $table_defs_loaded; # flag for each schema to indicate if all table_defs have been loaded for that schema
    protected $table_defs_refreshed; # flag for each table to indicate if a refresh has been tried in this process

    protected $persistent, $db_connection_name;
    protected static $db_connection_cache;

    protected static $sql_var = array();   # subclasses should define the list of SQL vars
    protected $instance_sql_var = array(); # instances should define the list of SQL vars

    public static $reserved_word = array(
        'a'=>1, 'abort'=>1, 'abs'=>1, 'absolute'=>1, 'access'=>1, 'action'=>1, 'ada'=>1, 'add'=>1, 'admin'=>1, 'after'=>1,
        'agent'=>1, 'aggregate'=>1, 'alias'=>1, 'all'=>1, 'allocate'=>1, 'allow'=>1, 'also'=>1, 'alter'=>1, 'always'=>1, 'analyse'=>1,
        'analyze'=>1, 'and'=>1, 'any'=>1, 'are'=>1, 'array'=>1, 'arrow'=>1, 'as'=>1, 'asc'=>1, 'asensitive'=>1, 'assertion'=>1,
        'assignment'=>1, 'associate'=>1, 'asutime'=>1, 'asymmetric'=>1, 'at'=>1, 'atomic'=>1, 'attribute'=>1, 'attributes'=>1, 'audit'=>1, 'authid'=>1,
        'authorization'=>1, 'auto_increment'=>1, 'aux'=>1, 'auxiliary'=>1, 'avg'=>1, 'avg_row_length'=>1, 'backup'=>1, 'backward'=>1, 'before'=>1, 'begin'=>1,
        'bernoulli'=>1, 'between'=>1, 'bfile_base'=>1, 'bigint'=>1, 'binary'=>1, 'bit'=>1, 'bit_length'=>1, 'bitvar'=>1, 'blob'=>1, 'blob_base'=>1,
        'block'=>1, 'body'=>1, 'bool'=>1, 'boolean'=>1, 'both'=>1, 'bound'=>1, 'breadth'=>1, 'break'=>1, 'browse'=>1, 'bufferpool'=>1,
        'bulk'=>1, 'by'=>1, 'byte'=>1, 'c'=>1, 'cache'=>1, 'call'=>1, 'called'=>1, 'calling'=>1, 'capture'=>1, 'cardinality'=>1,
        'cascade'=>1, 'cascaded'=>1, 'case'=>1, 'cast'=>1, 'catalog'=>1, 'catalog_name'=>1, 'ccsid'=>1, 'ceil'=>1, 'ceiling'=>1, 'chain'=>1,
        'change'=>1, 'char'=>1, 'character'=>1, 'characteristics'=>1, 'character_length'=>1,
        'characters'=>1, 'character_set_catalog'=>1, 'character_set_name'=>1, 'character_set_schema'=>1, 'char_base'=>1,
        'char_length'=>1, 'charset'=>1, 'charsetform'=>1, 'charsetid'=>1, 'check'=>1, 'checked'=>1, 'checkpoint'=>1, 'checksum'=>1, 'class'=>1, 'class_origin'=>1,
        'clob'=>1, 'clob_base'=>1, 'clone'=>1, 'close'=>1, 'cluster'=>1, 'clustered'=>1, 'clusters'=>1, 'coalesce'=>1, 'cobol'=>1, 'colauth'=>1,
        'collate'=>1, 'collation'=>1, 'collation_catalog'=>1, 'collation_name'=>1, 'collation_schema'=>1, 'collect'=>1, 'collection'=>1, 'collid'=>1, 'column'=>1, 'column_name'=>1,
        'columns'=>1, 'command_function'=>1, 'command_function_code'=>1, 'comment'=>1, 'commit'=>1, 'committed'=>1, 'compiled'=>1, 'completion'=>1, 'compress'=>1, 'compute'=>1,
        'concat'=>1, 'condition'=>1, 'condition_number'=>1, 'connect'=>1, 'connection'=>1, 'connection_name'=>1, 'constant'=>1, 'constraint'=>1, 'constraint_catalog'=>1, 'constraint_name'=>1,
        'constraints'=>1, 'constraint_schema'=>1, 'constructor'=>1, 'contains'=>1, 'containstable'=>1, 'content'=>1, 'context'=>1, 'continue'=>1, 'conversion'=>1, 'convert'=>1,
        'copy'=>1, 'corr'=>1, 'corresponding'=>1, 'count'=>1, 'covar_pop'=>1, 'covar_samp'=>1, 'crash'=>1, 'create'=>1, 'createdb'=>1, 'createrole'=>1,
        'createuser'=>1, 'cross'=>1, 'csv'=>1, 'cube'=>1, 'cume_dist'=>1, 'current'=>1, 'current_catalog'=>1, 'current_date'=>1, 'current_default_transform_group'=>1, 'current_lc_ctype'=>1,
        'current_path'=>1, 'current_role'=>1, 'current_schema'=>1, 'current_time'=>1, 'current_timestamp'=>1,
        'current_transform_group_for_type'=>1, 'current_user'=>1, 'cursor'=>1, 'cursor_name'=>1, 'customdatum'=>1,
        'cycle'=>1, 'dangling'=>1, 'data'=>1, 'database'=>1, 'databases'=>1, 'date'=>1, 'date_base'=>1, 'datetime'=>1, 'datetime_interval_code'=>1, 'datetime_interval_precision'=>1,
        'day'=>1, 'day_hour'=>1, 'day_microsecond'=>1, 'day_minute'=>1, 'dayofmonth'=>1, 'dayofweek'=>1, 'dayofyear'=>1, 'days'=>1, 'day_second'=>1, 'dbcc'=>1,
        'dbinfo'=>1, 'deallocate'=>1, 'dec'=>1, 'decimal'=>1, 'declare'=>1, 'default'=>1, 'defaults'=>1, 'deferrable'=>1, 'deferred'=>1, 'define'=>1,
        'defined'=>1, 'definer'=>1, 'degree'=>1, 'delayed'=>1, 'delay_key_write'=>1, 'delete'=>1, 'delimiter'=>1, 'delimiters'=>1, 'dense_rank'=>1, 'deny'=>1,
        'depth'=>1, 'deref'=>1, 'derived'=>1, 'desc'=>1, 'describe'=>1, 'descriptor'=>1, 'destroy'=>1, 'destructor'=>1, 'deterministic'=>1, 'diagnostics'=>1,
        'dictionary'=>1, 'disable'=>1, 'disallow'=>1, 'disconnect'=>1, 'disk'=>1, 'dispatch'=>1, 'distinct'=>1, 'distinctrow'=>1, 'distributed'=>1, 'div'=>1,
        'do'=>1, 'document'=>1, 'domain'=>1, 'double'=>1, 'drop'=>1, 'dssize'=>1, 'dual'=>1, 'dummy'=>1, 'dump'=>1, 'duration'=>1,
        'dynamic'=>1, 'dynamic_function'=>1, 'dynamic_function_code'=>1, 'each'=>1, 'editproc'=>1, 'element'=>1, 'else'=>1, 'elseif'=>1, 'elsif'=>1, 'empty'=>1,
        'enable'=>1, 'enclosed'=>1, 'encoding'=>1, 'encrypted'=>1, 'encryption'=>1, 'end'=>1, 'end-exec'=>1, 'ending'=>1, 'enum'=>1, 'equals'=>1,
        'erase'=>1, 'errlvl'=>1, 'escape'=>1, 'escaped'=>1, 'every'=>1, 'except'=>1, 'exception'=>1, 'exceptions'=>1, 'exclude'=>1, 'excluding'=>1,
        'exclusive'=>1, 'exec'=>1, 'execute'=>1, 'existing'=>1, 'exists'=>1, 'exit'=>1, 'exp'=>1, 'explain'=>1, 'external'=>1, 'extract'=>1,
        'false'=>1, 'fenced'=>1, 'fetch'=>1, 'fieldproc'=>1, 'fields'=>1, 'file'=>1, 'fillfactor'=>1, 'filter'=>1, 'final'=>1, 'first'=>1,
        'fixed'=>1, 'float'=>1, 'float4'=>1, 'float8'=>1, 'floor'=>1, 'flush'=>1, 'following'=>1, 'for'=>1, 'forall'=>1, 'force'=>1,
        'foreign'=>1, 'form'=>1, 'fortran'=>1, 'forward'=>1, 'found'=>1, 'free'=>1, 'freetext'=>1, 'freetexttable'=>1, 'freeze'=>1, 'from'=>1,
        'full'=>1, 'fulltext'=>1, 'fulltexttable'=>1, 'function'=>1, 'fusion'=>1, 'g'=>1, 'general'=>1, 'generated'=>1, 'get'=>1, 'global'=>1,
        'go'=>1, 'goto'=>1, 'grant'=>1, 'granted'=>1, 'grants'=>1, 'greatest'=>1, 'group'=>1, 'grouping'=>1, 'handler'=>1, 'hash'=>1,
        'having'=>1, 'header'=>1, 'heap'=>1, 'hidden'=>1, 'hierarchy'=>1, 'high_priority'=>1, 'hold'=>1, 'holdlock'=>1, 'host'=>1, 'hosts'=>1,
        'hour'=>1, 'hour_microsecond'=>1, 'hour_minute'=>1, 'hours'=>1, 'hour_second'=>1, 'identified'=>1, 'identity'=>1, 'identitycol'=>1, 'identity_insert'=>1, 'if'=>1,
        'ignore'=>1, 'ilike'=>1, 'immediate'=>1, 'immutable'=>1, 'implementation'=>1, 'implicit'=>1, 'in'=>1, 'include'=>1, 'including'=>1, 'inclusive'=>1,
        'increment'=>1, 'index'=>1, 'indexes'=>1, 'indicator'=>1, 'indices'=>1, 'infile'=>1, 'infinite'=>1, 'infix'=>1, 'inherit'=>1, 'inherits'=>1,
        'initial'=>1, 'initialize'=>1, 'initially'=>1, 'inner'=>1, 'inout'=>1, 'input'=>1, 'insensitive'=>1, 'insert'=>1, 'insert_id'=>1, 'instance'=>1,
        'instantiable'=>1, 'instead'=>1, 'int'=>1, 'int1'=>1, 'int2'=>1, 'int3'=>1, 'int4'=>1, 'int8'=>1, 'integer'=>1, 'interface'=>1,
        'intersect'=>1, 'intersection'=>1, 'interval'=>1, 'into'=>1, 'invalidate'=>1, 'invoker'=>1, 'is'=>1, 'isam'=>1, 'isnull'=>1, 'isobid'=>1,
        'isolation'=>1, 'iterate'=>1, 'jar'=>1, 'java'=>1, 'join'=>1, 'k'=>1, 'keep'=>1, 'key'=>1, 'key_member'=>1, 'keys'=>1,
        'key_type'=>1, 'kill'=>1, 'label'=>1, 'lancompiler'=>1, 'language'=>1, 'large'=>1, 'last'=>1, 'last_insert_id'=>1, 'lateral'=>1, 'lc_ctype'=>1,
        'leading'=>1, 'least'=>1, 'leave'=>1, 'left'=>1, 'length'=>1, 'less'=>1, 'level'=>1, 'library'=>1, 'like'=>1, 'like2'=>1,
        'like4'=>1, 'likec'=>1, 'like_regex'=>1, 'limit'=>1, 'limited'=>1, 'lineno'=>1, 'lines'=>1, 'listen'=>1, 'ln'=>1, 'load'=>1,
        'local'=>1, 'locale'=>1, 'localtime'=>1, 'localtimestamp'=>1, 'location'=>1, 'locator'=>1, 'locators'=>1, 'lock'=>1, 'lockmax'=>1, 'locksize'=>1,
        'login'=>1, 'logs'=>1, 'long'=>1, 'longblob'=>1, 'longtext'=>1, 'loop'=>1, 'lower'=>1, 'low_priority'=>1, 'm'=>1, 'maintained'=>1,
        'map'=>1, 'match'=>1, 'matched'=>1, 'materialized'=>1, 'max'=>1, 'maxextents'=>1, 'maxlen'=>1, 'max_rows'=>1, 'maxvalue'=>1, 'mediumblob'=>1,
        'mediumint'=>1, 'mediumtext'=>1, 'member'=>1, 'merge'=>1, 'message_length'=>1, 'message_octet_length'=>1, 'message_text'=>1, 'method'=>1, 'microsecond'=>1, 'microseconds'=>1,
        'middleint'=>1, 'min'=>1, 'min_rows'=>1, 'minus'=>1, 'minute'=>1, 'minute_microsecond'=>1, 'minutes'=>1, 'minute_second'=>1, 'minvalue'=>1, 'mlslabel'=>1,
        'mod'=>1, 'mode'=>1, 'modifies'=>1, 'modify'=>1, 'module'=>1, 'month'=>1, 'monthname'=>1, 'months'=>1, 'more'=>1, 'move'=>1,
        'multiset'=>1, 'mumps'=>1, 'myisam'=>1, 'name'=>1, 'names'=>1, 'nan'=>1, 'national'=>1, 'native'=>1, 'natural'=>1, 'nchar'=>1,
        'nclob'=>1, 'nesting'=>1, 'new'=>1, 'next'=>1, 'nextval'=>1, 'no'=>1, 'noaudit'=>1, 'nocheck'=>1, 'nocompress'=>1, 'nocopy'=>1,
        'nocreatedb'=>1, 'nocreaterole'=>1, 'nocreateuser'=>1, 'noinherit'=>1, 'nologin'=>1, 'nonclustered'=>1, 'none'=>1, 'normalize'=>1, 'normalized'=>1, 'nosuperuser'=>1,
        'not'=>1, 'nothing'=>1, 'notify'=>1, 'notnull'=>1, 'nowait'=>1, 'no_write_to_binlog'=>1, 'null'=>1, 'nullable'=>1, 'nullif'=>1, 'nulls'=>1,
        'number'=>1, 'number_base'=>1, 'numeric'=>1, 'numparts'=>1, 'obid'=>1, 'object'=>1, 'occurrences_regex'=>1, 'ocicoll'=>1, 'ocidate'=>1, 'ocidatetime'=>1,
        'ociduration'=>1, 'ociinterval'=>1, 'ociloblocator'=>1, 'ocinumber'=>1, 'ociraw'=>1, 'ociref'=>1, 'ocirefcursor'=>1, 'ocirowid'=>1, 'ocistring'=>1, 'ocitype'=>1,
        'octet_length'=>1, 'octets'=>1, 'of'=>1, 'off'=>1, 'offline'=>1, 'offset'=>1, 'offsets'=>1, 'oids'=>1, 'old'=>1, 'on'=>1,
        'online'=>1, 'only'=>1, 'opaque'=>1, 'open'=>1, 'opendatasource'=>1, 'openquery'=>1, 'openrowset'=>1, 'openxml'=>1, 'operation'=>1, 'operator'=>1,
        'optimization'=>1, 'optimize'=>1, 'option'=>1, 'optionally'=>1, 'options'=>1, 'or'=>1, 'oracle'=>1, 'oradata'=>1, 'order'=>1, 'ordering'=>1,
        'ordinality'=>1, 'organization'=>1, 'orlany'=>1, 'orlvary'=>1, 'others'=>1, 'out'=>1, 'outer'=>1, 'outfile'=>1, 'output'=>1, 'over'=>1,
        'overlaps'=>1, 'overlay'=>1, 'overriding'=>1, 'owner'=>1, 'package'=>1, 'pack_keys'=>1, 'pad'=>1, 'padded'=>1, 'parallel_enable'=>1, 'parameter'=>1,
        'parameter_mode'=>1, 'parameter_name'=>1, 'parameter_ordinal_position'=>1, 'parameters'=>1, 'parameter_specific_catalog'=>1,
        'parameter_specific_name'=>1, 'parameter_specific_schema'=>1, 'part'=>1, 'partial'=>1, 'partition'=>1,
        'partitioned'=>1, 'partitioning'=>1, 'pascal'=>1, 'password'=>1, 'path'=>1, 'pctfree'=>1, 'percent'=>1, 'percentile_cont'=>1, 'percentile_disc'=>1, 'percent_rank'=>1,
        'period'=>1, 'piecesize'=>1, 'pipe'=>1, 'pipelined'=>1, 'pivot'=>1, 'placing'=>1, 'plan'=>1, 'pli'=>1, 'position'=>1, 'position_regex'=>1,
        'postfix'=>1, 'power'=>1, 'pragma'=>1, 'preceding'=>1, 'precision'=>1, 'prefix'=>1, 'preorder'=>1, 'prepare'=>1, 'prepared'=>1, 'preserve'=>1,
        'prevval'=>1, 'primary'=>1, 'print'=>1, 'prior'=>1, 'priqty'=>1, 'private'=>1, 'privileges'=>1, 'proc'=>1, 'procedural'=>1, 'procedure'=>1,
        'process'=>1, 'processlist'=>1, 'program'=>1, 'psid'=>1, 'public'=>1, 'purge'=>1, 'query'=>1, 'queryno'=>1, 'quote'=>1, 'raid0'=>1,
        'raise'=>1, 'raiserror'=>1, 'range'=>1, 'rank'=>1, 'raw'=>1, 'read'=>1, 'reads'=>1, 'readtext'=>1, 'real'=>1, 'recheck'=>1,
        'reconfigure'=>1, 'record'=>1, 'recursive'=>1, 'ref'=>1, 'reference'=>1, 'references'=>1, 'referencing'=>1, 'refresh'=>1, 'regexp'=>1, 'regr_avgx'=>1,
        'regr_avgy'=>1, 'regr_count'=>1, 'regr_intercept'=>1, 'regr_r2'=>1, 'regr_slope'=>1, 'regr_sxx'=>1, 'regr_sxy'=>1, 'regr_syy'=>1, 'reindex'=>1, 'relative'=>1,
        'release'=>1, 'reload'=>1, 'rem'=>1, 'remainder'=>1, 'rename'=>1, 'repeat'=>1, 'repeatable'=>1, 'replace'=>1, 'replication'=>1, 'require'=>1,
        'reset'=>1, 'resignal'=>1, 'resource'=>1, 'restart'=>1, 'restore'=>1, 'restrict'=>1, 'result'=>1, 'result_set_locator'=>1, 'return'=>1, 'returned_cardinality'=>1,
        'returned_length'=>1, 'returned_octet_length'=>1, 'returned_sqlstate'=>1, 'returning'=>1, 'returns'=>1, 'reverse'=>1, 'revert'=>1, 'revoke'=>1, 'right'=>1, 'rlike'=>1,
        'role'=>1, 'rollback'=>1, 'rollup'=>1, 'round_ceiling'=>1, 'round_down'=>1, 'round_floor'=>1, 'round_half_down'=>1, 'round_half_even'=>1, 'round_half_up'=>1, 'round_up'=>1,
        'routine'=>1, 'routine_catalog'=>1, 'routine_name'=>1, 'routine_schema'=>1, 'row'=>1, 'rowcount'=>1, 'row_count'=>1, 'rowguidcol'=>1, 'rowid'=>1, 'rownum'=>1,
        'row_number'=>1, 'rows'=>1, 'rowset'=>1, 'rule'=>1, 'run'=>1, 'sample'=>1, 'save'=>1, 'savepoint'=>1, 'sb1'=>1, 'sb2'=>1,
        'sb4'=>1, 'scale'=>1, 'schema'=>1, 'schema_name'=>1, 'schemas'=>1, 'scope'=>1, 'scope_catalog'=>1, 'scope_name'=>1, 'scope_schema'=>1, 'scratchpad'=>1,
        'scroll'=>1, 'search'=>1, 'second'=>1, 'second_microsecond'=>1, 'seconds'=>1, 'secqty'=>1, 'section'=>1, 'security'=>1, 'securityaudit'=>1, 'segment'=>1,
        'select'=>1, 'self'=>1, 'semantickeyphrasetable'=>1, 'semanticsimilaritydetailstable'=>1, 'semanticsimilaritytable'=>1,
        'sensitive'=>1, 'separate'=>1, 'separator'=>1, 'sequence'=>1, 'serializable'=>1,
        'server_name'=>1, 'session'=>1, 'session_user'=>1, 'set'=>1, 'setof'=>1, 'sets'=>1, 'setuser'=>1, 'share'=>1, 'short'=>1, 'show'=>1,
        'shutdown'=>1, 'signal'=>1, 'similar'=>1, 'simple'=>1, 'size'=>1, 'size_t'=>1, 'smallint'=>1, 'some'=>1, 'soname'=>1, 'source'=>1,
        'space'=>1, 'sparse'=>1, 'spatial'=>1, 'specific'=>1, 'specific_name'=>1, 'specifictype'=>1, 'sql'=>1, 'sql_big_result'=>1, 'sql_big_selects'=>1, 'sql_big_tables'=>1,
        'sqlca'=>1, 'sql_calc_found_rows'=>1, 'sqlcode'=>1, 'sqldata'=>1, 'sqlerror'=>1, 'sqlexception'=>1, 'sql_log_off'=>1, 'sql_log_update'=>1, 'sql_low_priority_updates'=>1, 'sqlname'=>1,
        'sql_select_limit'=>1, 'sql_small_result'=>1, 'sqlstate'=>1, 'sqlwarning'=>1, 'sql_warnings'=>1, 'sqrt'=>1, 'ssl'=>1, 'stable'=>1, 'standard'=>1, 'start'=>1,
        'starting'=>1, 'state'=>1, 'statement'=>1, 'static'=>1, 'statistics'=>1, 'status'=>1, 'stay'=>1, 'stddev'=>1, 'stddev_pop'=>1, 'stddev_samp'=>1,
        'stdin'=>1, 'stdout'=>1, 'stogroup'=>1, 'storage'=>1, 'stored'=>1, 'stores'=>1, 'straight_join'=>1, 'strict'=>1, 'string'=>1, 'struct'=>1,
        'structure'=>1, 'style'=>1, 'subclass_origin'=>1, 'sublist'=>1, 'submultiset'=>1, 'subpartition'=>1, 'substitutable'=>1, 'substring'=>1, 'substring_regex'=>1, 'subtype'=>1,
        'successful'=>1, 'sum'=>1, 'summary'=>1, 'superuser'=>1, 'symmetric'=>1, 'synonym'=>1, 'sysdate'=>1, 'sysdate1sysfun'=>1, 'sysibm'=>1, 'sysid'=>1,
        'sysproc'=>1, 'system'=>1, 'system_user'=>1, 'systimestamp'=>1, 'tabauth'=>1, 'table'=>1, 'table_name'=>1, 'tables'=>1, 'tablesample'=>1, 'tablespace'=>1,
        'tdo'=>1, 'temp'=>1, 'template'=>1, 'temporary'=>1, 'terminate'=>1, 'terminated'=>1, 'text'=>1, 'textsize'=>1, 'than'=>1, 'the'=>1,
        'then'=>1, 'ties'=>1, 'time'=>1, 'timestamp'=>1, 'timezone_abbr'=>1, 'timezone_hour'=>1, 'timezone_minute'=>1, 'timezone_region'=>1, 'tinyblob'=>1, 'tinyint'=>1,
        'tinytext'=>1, 'to'=>1, 'toast'=>1, 'top'=>1, 'top_level_count'=>1, 'trailing'=>1, 'tran'=>1, 'transac'=>1, 'transaction'=>1, 'transaction_active'=>1,
        'transactional'=>1, 'transactions_committed'=>1, 'transactions_rolled_back'=>1, 'transform'=>1, 'transforms'=>1,
        'translate'=>1, 'translate_regex'=>1, 'translation'=>1, 'treat'=>1, 'trigger'=>1,
        'trigger_catalog'=>1, 'trigger_name'=>1, 'trigger_schema'=>1, 'trim'=>1, 'true'=>1,
        'truncate'=>1, 'trusted'=>1, 'try_convert'=>1, 'tsequal'=>1, 'type'=>1,
        'ub1'=>1, 'ub2'=>1, 'ub4'=>1, 'uescape'=>1, 'uid'=>1, 'unbounded'=>1, 'uncommitted'=>1, 'under'=>1, 'undo'=>1, 'unencrypted'=>1,
        'union'=>1, 'unique'=>1, 'unknown'=>1, 'unlisten'=>1, 'unlock'=>1, 'unnamed'=>1, 'unnest'=>1, 'unpivot'=>1, 'unsigned'=>1, 'until'=>1,
        'untrusted'=>1, 'update'=>1, 'updatetext'=>1, 'upper'=>1, 'usage'=>1, 'use'=>1, 'user'=>1, 'user_defined_type_catalog'=>1, 'user_defined_type_code'=>1, 'user_defined_type_name'=>1,
        'user_defined_type_schema'=>1, 'using'=>1, 'utc_date'=>1, 'utc_time'=>1, 'utc_timestamp'=>1, 'vacuum'=>1, 'valid'=>1, 'validate'=>1, 'validator'=>1, 'validproc'=>1,
        'valist'=>1, 'value'=>1, 'values'=>1, 'varbinary'=>1, 'varchar'=>1, 'varchar2'=>1, 'varcharacter'=>1, 'variable'=>1, 'variables'=>1, 'variance'=>1,
        'variant'=>1, 'var_pop'=>1, 'varray'=>1, 'var_samp'=>1, 'varying'=>1, 'vcat'=>1, 'verbose'=>1, 'view'=>1, 'views'=>1, 'void'=>1,
        'volatile'=>1, 'volumes'=>1, 'waitfor'=>1, 'when'=>1, 'whenever'=>1, 'where'=>1, 'while'=>1, 'width_bucket'=>1, 'window'=>1, 'with'=>1,
        'within'=>1, 'without'=>1, 'wlm'=>1, 'work'=>1, 'wrapped'=>1, 'write'=>1, 'writetext'=>1, 'x509'=>1, 'xmlagg'=>1, 'xmlattributes'=>1,
        'xmlbinary'=>1, 'xmlcast'=>1, 'xmlcomment'=>1, 'xmlconcat'=>1, 'xmldocument'=>1, 'xmlelement'=>1, 'xmlexists'=>1, 'xmlforest'=>1, 'xmliterate'=>1, 'xmlnamespaces'=>1,
        'xmlparse'=>1, 'xmlpi'=>1, 'xmlquery'=>1, 'xmlserialize'=>1, 'xmltable'=>1, 'xmltext'=>1, 'xmlvalidate'=>1, 'xor'=>1, 'year'=>1, 'year_month'=>1,
        'years'=>1, 'zerofill'=>1, 'zone'=>1, 'zoneadd'=>1
    );

    #################################################################
    # Constructor
    #################################################################
    public function __construct($context, $type, $name, $conf) {
        global $trace;
        if ($trace) trace_entry();
        parent::__construct($context, $type, $name, $conf);
        if (!isset($conf['dbtype'])) throw new \Exception("Database [$this->name] has no dbtype");
        if (!isset($conf['db_env'])) throw new \Exception("Database [$this->name] has no db_env");
        $this->dbtype = $conf['dbtype'];
        $this->db_env = $conf['db_env'];

        if (isset($conf['db_name']))            $this->db_name            = $conf['db_name'];
        else                                    $this->db_name            = $name;
        if (isset($conf['dbhost']))             $this->dbhost             = $conf['dbhost'];
        if     (isset($conf['schema_name']))    $this->schema_name        = $conf['schema_name'];
        elseif (isset($conf['default_schema'])) $this->schema_name        = $conf['default_schema'];
        if (isset($conf['dbuser']))             $this->dbuser             = $conf['dbuser'];
        if (isset($conf['dbpass']))             $this->dbpass             = $conf['dbpass'];
        if (isset($conf['dboptions']))          $this->dboptions          = $conf['dboptions'];
        if (isset($conf['owner_group_id']))     $this->owner_group_id     = $conf['owner_group_id'];
        if (isset($conf['db_owner_group_id']))  $this->db_owner_group_id  = $conf['db_owner_group_id'];
        elseif (isset($conf['owner_group_id'])) $this->db_owner_group_id  = $conf['owner_group_id'];
        else                                    $this->db_owner_group_id  = APP_ALLUSERS_GROUP_ID;

        $this->_parse_dboptions();
        if (!isset($this->schema_name)) {
            $this->schema_name  = $this->_assumed_schema_name();   # schema_name MUST be set for get_table_def() to work
        }

        static::$sql_var["${name}_schema"]      = $this->schema_name;
        static::$sql_var["${name}_schema_"]     = $this->schema_name . '.';
        $this->instance_sql_var['this_schema']  = $this->schema_name;
        $this->instance_sql_var['this_schema_'] = $this->schema_name . '.';

        $owner_group_id = $this->context->getAuthenticatedGroupId();
        #debug_print("XXX Database.__construct($this->name): owner_group_id=[$owner_group_id] db_owner_group_id=[$this->db_owner_group_id] db_name=[$this->db_name]");
        if ($this->db_name === META_DB_NAME) {
            $this->instance_sql_var['meta_schema']  = $this->schema_name;
            $this->instance_sql_var['meta_schema_'] = $this->schema_name . '.';
        }

        $this->persistent        = (isset($conf['persistent']) && $conf['persistent']);
        if (isset($conf['db_id']))              $this->db_id              = $conf['db_id'];
        if (isset($conf['db_connection_id']))   $this->db_connection_id   = $conf['db_connection_id'];
        if (isset($conf['db_connection_name'])) $this->db_connection_name = $conf['db_connection_name'];
        if (!isset(self::$db_connection_cache)) {
            self::$db_connection_cache = array();
            #debug_print("CACHING: initializing cache (setting to empty array)");
        }
        #debug_print("CACHING: ".print_r(self::$db_connection_cache, true));

        $this->dsn = (isset($conf['dsn'])) ? $conf['dsn'] : $this->_dsn();
        #echo "Database::__construct(): name=[$name] dbtype=[$this->dbtype] dbhost=[$this->dbhost] schema_name=[$this->schema_name] dsn=[$this->dsn] dbuser=[$this->dbuser] dbpass=[xxx] dboptions=[$this->dboptions] db_id=[$this->db_id]\n";
        $this->table_defs        = array();
        $this->table_defs_loaded = array();
        $this->_postconstruct();
        $this->_connect();
        if ($trace) trace_exit();
    }
    protected function _postconstruct () {}

    #################################################################
    # Accessors
    #################################################################
    public function dbtype () { return($this->dbtype); }

    #################################################################
    # Methods
    #################################################################
    private function _parse_dboptions () {
        global $trace;
        if ($trace) trace_entry();
        $this->dboption = array();
        if (isset($this->dboptions)) {
            $matches = array();
            $dboption_settings = explode(',', $this->dboptions);
            foreach ($dboption_settings as $dboption_setting) {
                if (preg_match('/^([^=])+=(.*)$/', $dboption_setting, $matches)) {
                    $this->dboption[$matches[1]] = $matches[2];
                }
            }
        }
        if ($trace) trace_exit();
    }

    # NOTE: Oracle assumes the schema_name is the dbuser. SQLite assumes it is 'main'. MySQL requires it be set. All db's will assume it is the default_schema if not otherwise set.
    # (Override this for Oracle and other db's that need it.)
    protected function _assumed_schema_name() {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->dbtype) && $this->dbtype === 'oracle') {
            if (!isset($this->dbuser)) throw new \Exception("Database [$this->name] has no schema_name and no dbuser whereby it may be inferred");
            $schema_name = $this->dbuser;
        }
        elseif (isset($this->dbtype) && $this->dbtype === 'sqlite') {
            $schema_name = 'main';
        }
        else {
            throw new \Exception("Database [$this->name] has no schema_name and no means whereby it may be inferred");
        }
        if ($trace) trace_exit($schema_name);
        return($schema_name);
    }

    protected function metadb () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->metadb)) {
            $metadb = $this->metadb;
        }
        else {
            if ($this->db_name === META_DB_NAME) {
                $metadb = $this;
            }
            else {
                $owner_group_id = $this->context->getAuthenticatedGroupId();
                if ($this->db_owner_group_id == $owner_group_id) {
                    $db_env  = $this->db_env;
                    $meta_name = META_DB_NAME;
                }
                else {
                    $db_env  = 'prod';
                    $meta_name = META_DB_NAME . '_' . $this->db_owner_group_id;
                }
                if ($meta_name === $this->name) {
                    $metadb = $this;
                }
                else {
                    $metadb = $this->context->database($meta_name, array('owner_group_id' => $this->db_owner_group_id, 'db_env' => $db_env, 'db_name' => META_DB_NAME));
                }
            }
            $this->instance_sql_var['meta_schema']  = $metadb->schema_name;
            $this->instance_sql_var['meta_schema_'] = $metadb->schema_name . '.';
            $this->metadb = $metadb;
            #debug_print('XXX metadb(): meta_name=[$meta_name] name=[$this->name] ' . print_r($this->instance_sql_var,true) . print_r(static::$sql_var,true));
        }
        if ($trace) trace_exit($metadb);
        return($metadb);
    }

    protected function _connect () {
    }

    protected function _disconnect () {}
    public function create_table ($table_def) {}
    public function drop_table ($table_name) {}
    public function rename_table ($table_name, $new_table_name, $new_table_label) {
        $this->_rename_table_cache ($table_name, $new_table_name, $new_table_label);
    }
    public function alter_table ($table_def, $table_def_from_db = null) {}
    public function export_table ($table_name, $filename = null, $options = null) {}
    public function import_table ($table_name, $filename = null, $options = null) {}
    protected function _get_column_defs($table_name, $schema_name, &$table_def) { return(array()); }
    protected function _get_indexes($table_name, $schema_name, &$table_def) {}
    protected function _get_native_column_defs($table_name, $schema_name) { return(array()); }
    public function get_rows ($table_name, $params = null, $columns = null, &$options = null) { return(array()); }
    public function get_hashes ($table_name, $params = null, $columns = null, &$options = null) { return(array()); }
    public function get_column ($table_name, $params = null, $column = null, &$options = null) { return(array()); }

    public function get_schema_name () {
        return($this->schema_name);
    }

    public function get_schema_def ($schema_name) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($this->schema_defs))          $this->_load_schema_defs($schema_name);
        if (!isset($this->schema_defs[$schema_name])) throw new \Exception("get_schema_def($schema_name): schema is unknown or does not exist [".__LINE__."]");
        if ($trace) trace_exit($this->schema_defs[$schema_name]);
        return($this->schema_defs[$schema_name]);
    }

    public function get_schema_names () {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($this->schema_defs))          $this->_load_schema_defs();
        if ($trace) trace_exit(array_keys($this->schema_defs));
        return(array_keys($this->schema_defs));
    }

    public function get_owner_group_id () {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->owner_group_id)) $owner_group_id = $this->owner_group_id;
        else {
            $metadb = $this->metadb();
            $owner_group_id = $metadb->get('select owner_group_id from {auth_schema_}db where db_id = ?', array($this->db_id));
            $this->owner_group_id = $owner_group_id;
        }
        if ($trace) trace_exit($owner_group_id);
        return($owner_group_id);
    }

    private function _load_schema_defs ($schema_name = null) {
        global $trace, $context;
        if ($trace) trace_entry();
        $metadb   = $this->metadb();

        # Load the metadata for all instances from metadb
        if (!isset($this->schema_defs)) {
            $this->schema_defs = $metadb->get_hash_of_hashes_by_key("select * from {meta_schema_}db_schema where db_connection_id = ? and schema_name = ? order by schema_name", array($this->db_connection_id, $this->schema_name), null, 'schema_name');

            # HEAL THE METADATA
            if (count($this->schema_defs) == 0 || !isset($this->schema_defs[$this->schema_name])) {

                # Heal the db_connection table if necessary
                $metadb_db_connection_id = $metadb->get("select db_connection_id from {meta_schema_}db_connection where db_connection_id = ?", array($this->db_connection_id));
                if (!isset($metadb_db_connection_id)) {
                    $db_connection = $authdb->get_hash("select db_connection_id db_connection_name, db_instance_id, db_owner_group_id, dbtype, dbhost, default_schema, db_class, dsn, dbuser, dbpass, dboptions, order_idx, user_mode, group_mode, other_mode, modify_user_id from {auth_schema_}db_connection where db_connection_id = ?", array($this->db_connection_id));
                    $raw = array('raw'=>1);
                    $metadb->insert("db_connection", $db_connection, null, $raw);
                }

                # Heal the db table if necessary
                $metadb_db_id = $metadb->get("select db_id from {meta_schema_}db where db_id = ?", array($this->db_id));
                if (!isset($metadb_db_id)) {
                    $sql = <<<EOF
insert into {meta_schema_}db (db_id, owner_group_id, db_owner_group_id, db_env, db_name, db_label, dbtype,  db_connection_id, schema_name, status, user_mode, group_mode, other_mode, db_desc) values
               (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
EOF;
                    $metadb->execute($sql, array($this->db_id, $this->owner_group_id, $this->db_owner_group_id, $this->db_env, $this->db_name,
                        isset($this->conf['db_label']) ?  $this->conf['db_label'] : null,
                        $this->dbtype, $this->db_connection_id, $this->schema_name,
                        isset($this->conf['status']) ?  $this->conf['status'] : 'A',
                        isset($this->conf['user_mode']) ?  $this->conf['user_mode'] : null,
                        isset($this->conf['group_mode']) ?  $this->conf['group_mode'] : null,
                        isset($this->conf['other_mode']) ?  $this->conf['other_mode'] : null,
                        isset($this->conf['db_desc']) ?  $this->conf['db_desc'] : null));
                }

                $db_group_id = $this->db_owner_group_id;
                $sql = "insert into {meta_schema_}db_schema (db_connection_id, schema_name, schema_label, db_owner_group_id) values (?, ?, ?, ?)";
                $schema_label = $this->make_label($this->schema_name);
                $metadb->execute($sql, array($this->db_connection_id, $this->schema_name, $schema_label, $db_group_id));
                $schema_id = $metadb->last_insert_id('db_schema');
                $this->schema_defs[$this->schema_name] = array(
                    'schema_id' => $schema_id,
                    'db_connection_id' => $this->db_connection_id,
                    'schema_name' => $this->schema_name,
                    'schema_label' => $schema_label,
                    'db_owner_group_id' => $db_group_id
                );
            }
        }

        if ($trace) trace_exit();
    }

    ############################################################################################
    # TABLES and their TABLE-DETAILS (columns, indexes, relationships)
    ############################################################################################
    # these need to be implemented in the subclass
    protected function _get_table_defs_from_db ($schema_name) {
        $table_defs = array();
        return($table_defs);
    }

    public function get_table_def_from_db ($table_name, $schema_name) {
        $table_def = null;
        return($table_def);
    }

    public function &get_table_def ($table_name, $schema_name = null, &$options = null) {
        global $trace, $context;
        if ($trace) trace_entry();

        ########################################################################################
        # parse the optional schema component out of the $table
        ########################################################################################
        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table_name, $matches)) {
            $schema_name    = $matches[1];
            $fulltable_name = $table_name;
            $table_name     = $matches[2];
        }
        else {
            if (!isset($schema_name)) $schema_name = $this->schema_name;
            $fulltable_name = "$schema_name.$table_name";
        }

        ########################################################################################
        # load thin table_defs for all tables in the schema (attributes of the tables themselves)
        ########################################################################################
        if (!isset($this->table_defs[$schema_name]) || !isset($this->table_defs[$schema_name][$table_name])) {
             $this->_load_table_def($schema_name, $table_name);  # ... then load it and ensure that it loaded
             if (!isset($this->table_defs[$schema_name])) throw new \Exception("get_table_def($schema_name.$table_name): schema is unknown or does not exist [".__LINE__."]");
             if (!isset($this->table_defs[$schema_name][$table_name])) throw new \Exception("get_table_def($schema_name.$table_name): table is unknown or does not exist [".__LINE__."]");
             if (empty($this->table_defs[$schema_name][$table_name])) throw new \Exception("get_table_def($schema_name.$table_name): table is unknown or does not exist [".__LINE__."]");
        }

        $table_def =& $this->table_defs[$schema_name][$table_name];

        ########################################################################################
        # check to see if the table_def has view options that need to be integrated
        #    * display_width (per column)
        #    * hidden (per column)
        #    * sort (whole table)
        #    * column order (whole table)
        #    * filters (per column) (FUTURE)
        ########################################################################################

        $view_label        = null;     # the label of the view
        $view_options      = null;     # the decoded blob with stored view options
        $num_view_elements = 0;        # 0=none-found, 3=view options for the base table, 4=custom view of the table
        $view_columns      = null;     # array of column names in the order of the view options
        $used_columns      = null;     # array of column names visible, filtered, or ordered
        #$visible_columns  = null;     # array of visible column names
        $view_column_defs  = null;     # view options indexed by column_name
        $columns           = (isset($options['columns']) && $options['columns']) ? $options['columns'] : null;

        # if (isset($request['view']))          { $get_options['view']          = $request['view'];          }
        # if (isset($request['relationship']))  { $get_options['relationship']  = $request['relationship'];  }
        # if (isset($request['aggregate']))     { $get_options['aggregate']     = $request['aggregate'];     }
        # if (isset($request['columns']))       { $get_options['columns']       = $request['columns'];       }
        # if (isset($request['group_columns'])) { $get_options['group_columns'] = $request['group_columns']; }
        if (isset($options) && isset($options['view'])) {
            $view = $options['view'];
            $view_elements = explode('.', $view);
            $num_view_elements = count($view_elements);
            $metadb = $this->metadb();
            list($view_data, $view_label) = $metadb->get_row("select data, view_label from {meta_schema_}db_view where view_key = '$view'");

            if ($view_data) {
                $view_data = str_replace('\\"', '"', $view_data);    # Ouch. Temporary work-around. PHP 5.3.5 json_decode() on Mac barfs on the escape characters.
                $view_options = json_decode($view_data, true);
                #$this->overlay_table_def($table_def, $view_options);  # TODO: needs new home

                if (isset($view_options['columns'])) {
                    $view_stored_column_defs_array = $view_options['columns'];

                    $view_columns = array();
                    #$visible_columns = array();
                    $view_column_defs = array();

                    foreach ($view_stored_column_defs_array as $view_stored_column_def) {
                        $used = 0;
                        $view_column_def = array();
                        
                        if (!isset($view_stored_column_def['name'])) continue;
                        $column_name = $view_stored_column_def['name'];

                        if (isset($view_stored_column_def['width'])) {
                            $view_column_def['display_width'] = $view_stored_column_def['width'];
                        }
                        if (isset($view_stored_column_def['sort']) && $view_stored_column_def['sort']) {
                            $view_column_def['sort'] = $view_stored_column_def['sort'];
                            $used = 1;
                        }
                        if (isset($view_stored_column_def['filter']) && $view_stored_column_def['filter']) {
                            $view_column_def['filter'] = $view_stored_column_def['filter'];
                            $used = 1;
                        }
                        if (isset($view_stored_column_def['column_label']) && $view_stored_column_def['column_label']) {
                            $view_column_def['column_label'] = $view_stored_column_def['column_label'];
                        }
                        if (isset($view_stored_column_def['column_modifier']) && $view_stored_column_def['column_modifier']) {
                            $view_column_def['column_modifier'] = $view_stored_column_def['column_modifier'];
                        }
                        if (isset($view_stored_column_def['display_format']) && $view_stored_column_def['display_format']) {
                            $view_column_def['display_format'] = $view_stored_column_def['display_format'];
                        }
                        if (isset($view_stored_column_def['display_justify']) && $view_stored_column_def['display_justify']) {
                            $view_column_def['display_justify'] = $view_stored_column_def['display_justify'];
                        }
                        if (isset($view_stored_column_def['aggregate'])) {
                            $view_column_def['aggregate'] = ($view_stored_column_def['aggregate'] && $view_stored_column_def['aggregate'] !== 'false') ? true : false;
                        }
                        if (isset($view_stored_column_def['hidden']) && $view_stored_column_def['hidden']) {
                            $view_column_def['hidden'] = true;
                        }
                        else {
                            #$visible_columns[] = $column_name;
                            $used = 1;
                        }
                        if ($used) {
                            $used_columns[] = $column_name;
                        }

                        $view_columns[] = $column_name;
                        $view_column_defs[$column_name] = $view_column_def;
                    }
                }
                #if ($num_view_elements == 3) {
                #    $view_columns = $used_columns;
                #}
                if (isset($view_options['aggregate']) && !isset($options['aggregate'])) {
                    $options['aggregate'] = $view_options['aggregate'] ? 1 : 0;
                }
                if (isset($view_options['group_columns']) && !isset($options['group_columns'])) {
                    $options['group_columns'] = $view_options['group_columns'];
                }
                if (isset($view_options['data_columns']) && !isset($options['data_columns'])) {
                    $options['data_columns'] = $view_options['data_columns'];
                }
                if (isset($view_options['keys_horiz']) && !isset($options['keys_horiz'])) {
                    $options['keys_horiz'] = $view_options['keys_horiz'];
                }
            }
        }
        if (isset($options['aggregate'])) {
            $table_def['aggregate'] = ($options['aggregate'] && $options['aggregate'] !== 'false') ? true : false;
        }
        if (isset($options['group_columns'])) {
            $table_def['group_columns'] = $options['group_columns'];
        }
        if (isset($options['data_columns'])) {
            $table_def['data_columns'] = $options['data_columns'];
        }
        if (isset($options['keys_horiz'])) {
            $table_def['keys_horiz'] = $options['keys_horiz'];
        }

        ########################################################################################
        # load deep table_def info for this particular table (columns, indexes, etc.)
        ########################################################################################
        if (!isset($table_def['column']) || empty($table_def['column'])) {
            $this->_load_table_def_details($schema_name, $table_name, $options);
        }
        if (!isset($table_def['column']) || empty($table_def['column'])) {
            $table_def_from_db = $this->get_table_def_from_db($table_name, $this->schema_name);

            if (!isset($this->table_defs_refreshed)) {
                $refresh_ok = 1;
                $this->table_defs_refreshed = array( $schema_name => array($table_name => 1));
            }
            elseif (!isset($this->table_defs_refreshed[$schema_name])) {
                $refresh_ok = 1;
                $this->table_defs_refreshed[$schema_name] = array($table_name => 1);
            }
            elseif (!isset($this->table_defs_refreshed[$schema_name][$table_name])) {
                $refresh_ok = 1;
                $this->table_defs_refreshed[$schema_name][$table_name] = 1;
            }
            else {
                $refresh_ok = 0;
            }

            if ($refresh_ok && isset($table_def_from_db)) {
                $this->refresh_table_metadata($this->schema_name, $table_name, $table_def_from_db);
            }
            if (!isset($this->table_defs[$schema_name][$table_name])) throw new \Exception("get_table_def($schema_name.$table_name): table is unknown or does not exist [".__LINE__."]");
        }

        #########################################################################################
        # extend the table_def with requested relationships and view columns
        #########################################################################################
        if (isset($table_def['relationship'])) {
            if ($options) {
                if (isset($options['relationships']) && $options['relationships']) {
                    $rels = explode(',',$options['relationships']);
                    $rels_needed = array();
                    foreach ($rels as $rel) {
                        if (isset($table_def['relationship'][$rel]) && !isset($table_def['relationship'][$rel]['loaded'])) {
                            $rels_needed[] = $rel;
                            $table_def['relationship'][$rel]['loaded'] = 1;
                        }
                    }
                    if (count($rels_needed) > 0)
                        $this->_load_extra_column_defs($schema_name, $table_name, $rels_needed);
                }
            }
        }

        $cols_needed = array();
        $modified_cols_needed = array();

        if ($view_options || $columns) {
            $column_defs =& $table_def['column'];
            if ($view_options) {
                foreach ($view_columns as $column_name) {
                    $column_modifier = null;
                    if (preg_match('/^(.*)__([0-9]*[a-z]+)$/', $column_name, $matches)) {
                        $column_realname = $matches[1];
                        $column_modifier = $matches[2];
                        if (!isset($column_defs[$column_name])) {
                            $modified_cols_needed[$column_name] = array('column_name' => $column_name, 'parent'=>$column_realname, 'column_modifier'=>$column_modifier);
                        }
                        if (!isset($column_defs[$column_realname])) {
                            $cols_needed[] = $column_realname;
                        }
                    }
                    else {
                        if (!isset($column_defs[$column_name])) {
                            $cols_needed[] = $column_name;
                        }
                    }
                }
            }
            if (isset($columns)) {
                foreach ($columns as $column_name) {
                    $column_modifier = null;
                    if (preg_match('/^(.*)__([0-9]*[a-z]+)$/', $column_name, $matches)) {
                        $column_realname = $matches[1];
                        $column_modifier = $matches[2];
                        if (!isset($column_defs[$column_name]) && !isset($modified_cols_needed[$column_name])) {
                            $modified_cols_needed[$column_name] = array('column_name' => $column_name, 'parent'=>$column_realname, 'column_modifier'=>$column_modifier);
                        }
                        if (!isset($column_defs[$column_realname]) && !in_array($column_realname, $cols_needed)) {
                            $cols_needed[] = $column_realname;
                        }
                    }
                    else {
                        if (!isset($column_defs[$column_name]) && !in_array($column_realname, $cols_needed)) {
                            $cols_needed[] = $column_name;
                        }
                    }
                }
            }
            if (count($cols_needed) > 0) {
                $this->_load_extra_column_defs($schema_name, $table_name, null, $cols_needed);
            }
            if ($view_columns) {
                foreach ($view_columns as $column_name) {
                    if (isset($column_defs[$column_name]) && isset($view_column_defs[$column_name])) {
                        if (isset($view_column_defs[$column_name]['column_label']))
                            $column_defs[$column_name]['column_label']    = $view_column_defs[$column_name]['column_label'];
                        if (isset($view_column_defs[$column_name]['display_format']))
                            $column_defs[$column_name]['display_format']  = $view_column_defs[$column_name]['display_format'];
                        if (isset($view_column_defs[$column_name]['display_justify']))
                            $column_defs[$column_name]['display_justify'] = $view_column_defs[$column_name]['display_justify'];
                        if (isset($view_column_defs[$column_name]['display_width']))
                            $column_defs[$column_name]['display_width']   = $view_column_defs[$column_name]['display_width'];
                        if (isset($view_column_defs[$column_name]['aggregate']))
                            $column_defs[$column_name]['aggregate']       = $view_column_defs[$column_name]['aggregate'];
                        if (isset($view_column_defs[$column_name]['hidden']))
                            $column_defs[$column_name]['hidden']          = $view_column_defs[$column_name]['hidden'];
                        if (isset($view_column_defs[$column_name]['sort']))
                            $column_defs[$column_name]['sort']            = $view_column_defs[$column_name]['sort'];
                        if (isset($view_column_defs[$column_name]['filter']))
                            $column_defs[$column_name]['filter']          = $view_column_defs[$column_name]['filter'];
                        if (isset($view_column_defs[$column_name]['column_modifier']))
                            $column_defs[$column_name]['column_modifier'] = $view_column_defs[$column_name]['column_modifier'];
                    }
                }
            }

            if (isset($view_columns)) {
                # preserve the order of the columns
                $this->_enhance_columns($view_columns, $options);
                $table_def['columns'] = $view_columns;
            }
            if ($view_label) {
                $table_def['table_label'] = $view_label;
            }
            if ($view_options && isset($view_options['sql']))       $table_def['sql']       = $view_options['sql'];
        }

        if (count($modified_cols_needed) > 0) {
            foreach ($modified_cols_needed as $column_name => $column_def) {
                if (!isset($table_def['column'][$column_name])) {
                    $table_def['column'][$column_name] = $column_def;
                }
            }
        }

        if (isset($options['table_def'])) {
            $table_def_overrides = $options['table_def'];
            if (is_scalar($table_def_overrides)) {
                $table_def_overrides = json_decode($table_def_overrides, true);
            }
            $this->overlay_table_def($table_def, $table_def_overrides);
        }

        #########################################################################################
        # add the list of column names in order
        #########################################################################################
        if (!isset($table_def['columns'])) {
            if (isset($table_def['column'])) {
                $column_defs = $table_def['column'];
                $columns = array();
                foreach ($column_defs as $column_name => $column_def) {
                    $columns[] = $column_name;
                }
                $this->_enhance_columns($columns, $options);
                $table_def['columns'] = $columns;
            }
        }
        if (isset($columns)) {
            $table_def['columns'] = $columns;
        }

        #########################################################################################
        # add writable_ind, agg_key_ind
        #########################################################################################
        # * @param string  sort (form 1)   - [IN] the value for sort may be in either of two forms: (form 1) a JSON-encoded array of associative arrays which are called sorts.
        # *          e.g. [{"property":"log_date","direction":"DESC"},{"property":"remote_addr","direction":"ASC"}]
        # *        > property :   (required) the name of the sort. generally, this is the column name of the table being sorted.
        # *        > direction :  (optional) the direction of the sort. This should be 'ASC' or 'DESC' to sort forward/ascending or backward/descending. (default is ASC)
        # * @param string  sort (form 2)   - [IN] (form 2) a comma-separated list of columns to be sorted, optionally with direction specifiers appended to the column names.
        # *          e.g. log_date.DESC,remote_addr.ASC
        # * @param string  filter          - [IN] a JSON-encoded array of associative arrays which are each called filters. Each filter contains the following attributes.
        # *          e.g. [{"property":"remote_addr","value":"184.47.47.50","type":"string","operator":"eq"}]
        # *        > property :   (required) the name of the filter. generally, this is the column name of the table being filtered.
        # *        > value :      (required) the value of the filter. generally, this is the column name of the table being filtered.
        if (isset($table_def['column'])) {
            $filter = array();
            $sort   = array();
            foreach ($table_def['column'] as $column_name => $column_def) {

                if ($this->is_key($column_name, $column_def)) {
                    $table_def['column'][$column_name]['agg_key_ind'] = 'Y';
                }

                $auto_fill = ((isset($column_def['auto_increment_ind'])   && $column_def['auto_increment_ind']   === 'Y') ||
                              (isset($column_def['primary_key_ind'])      && $column_def['primary_key_ind']      === 'Y' && $column_def['column_type'] === 'integer' && 
                               (($this->dbtype === 'oracle' && $suffix === '_id') || $this->dbtype === 'sqlite')) ||
                              (isset($column_def['auto_modify_dttm_ind']) && $column_def['auto_modify_dttm_ind'] === 'Y') ||
                              $column_name === 'owner_group_id');
                $physical  = (isset($column_def['physical_ind']) && $column_def['physical_ind'] === 'Y');
                if ($physical && !$auto_fill) {
                    $table_def['column'][$column_name]['writable_ind'] = 'Y';
                }
                if (isset($column_def['sort'])) {
                    $direction = ($column_def['sort'] === 'DESC') ? 'DESC' : 'ASC';
                    $sort[] = array('property'=>$column_name,'direction'=>$direction);
                }
                if (isset($column_def['filter'])) {
                    $column_filter = array('property'=>$column_name,'value'=>$column_def['filter']['value']);
                    if (isset($column_def['filter']['operator'])) $column_filter['operator'] = $column_def['filter']['operator'];
                    if (isset($column_def['filter']['type'])) $column_filter['type'] = $column_def['filter']['type'];
                    $filter[] = $column_filter;
                }
            }
            if (count($sort) > 0) {
                $table_def['sort'] = $sort;
            }
            if (count($filter) > 0) {
                $table_def['filter'] = $filter;
            }
        }

        if ($trace) trace_exit($table_def);
        return($table_def);
    }

    protected function _rename_table_cache ($table_name, $new_table_name, $new_table_label) {
        global $trace;
        if ($trace) trace_entry();
        $schema_name = $this->schema_name;
        if (isset($this->table_defs[$schema_name])) {   # table_defs have been loaded
            if (!isset($this->table_defs[$schema_name][$table_name])) throw new \Exception("ERROR: table '$table_name' is not known");
            if (isset($this->table_defs[$schema_name][$new_table_name])) throw new \Exception("ERROR: table '$new_table_name' is already known");
            $this->table_defs[$schema_name][$new_table_name] = $this->table_defs[$schema_name][$table_name];
            $this->table_defs[$schema_name][$new_table_name]['table_name']  = $new_table_name;
            $this->table_defs[$schema_name][$new_table_name]['table_label'] = $new_table_label;
            unset($this->table_defs[$schema_name][$table_name]);
        }
        
        if ($trace) trace_exit();
    }

    private function overlay_table_def (&$table_def, $table_def_overrides) {
        global $trace;
        if ($trace) trace_entry();

        if (isset($table_def_overrides['column'])) {
            $column_defs          =& $table_def['column'];
            $override_column_defs =  $table_def_overrides['column'];
            foreach ($override_column_defs as $column_name => $override_column_def) {
                if (isset($column_defs[$column_name])) {
                    $column_def =& $column_defs[$column_name];
                    if (isset($override_column_def['width'])) {
                        $column_def['display_width'] = $override_column_def['width'];
                    }
                    if (isset($override_column_def['sort']) && $override_column_def['sort']) {
                        $column_def['sort'] = $override_column_def['sort'];
                    }
                    if (isset($override_column_def['filter']) && $override_column_def['filter']) {
                        $column_def['filter'] = $override_column_def['filter'];
                    }
                    if (isset($override_column_def['column_label']) && $override_column_def['column_label']) {
                        $column_def['column_label'] = $override_column_def['column_label'];
                    }
                    if (isset($override_column_def['column_modifier']) && $override_column_def['column_modifier']) {
                        $column_def['column_modifier'] = $override_column_def['column_modifier'];
                    }
                    if (isset($override_column_def['display_format']) && $override_column_def['display_format']) {
                        $column_def['display_format'] = $override_column_def['display_format'];
                    }
                    if (isset($override_column_def['display_justify']) && $override_column_def['display_justify']) {
                        $column_def['display_justify'] = $override_column_def['display_justify'];
                    }
                    if (isset($override_column_def['aggregate'])) {
                        $column_def['aggregate'] = ($override_column_def['aggregate'] && $override_column_def['aggregate'] !== 'false') ? true : false;
                    }
                    if (isset($override_column_def['hidden']) && $override_column_def['hidden']) {
                        $column_def['hidden'] = true;
                    }
                    else {
                        #$visible_columns[] = $column_name;
                    }
                }
            }
        }
        if ($trace) trace_exit();
    }

    public function is_key ($column, $column_def = null) {
        $is_key = 1;
        # TODO: fix this
        if (!isset($column_def))
            throw new \Exception("ERROR: is_key(): column_def parameter must be supplied");
        $column_modifier = (isset($column_def['column_modifier'])) ? $column_def['column_modifier'] : null;
        $column_type     = (isset($column_def['column_type']))     ? $column_def['column_type']     : null;
        $agg_dbexpr      = (isset($column_def['agg_dbexpr']))      ? $column_def['agg_dbexpr']      : null;
        if (isset($column_modifier)) {
            $is_key = preg_match('/^(sum|min|max|avg|stddev|count|countd)$/', $column_modifier) ? 0 : 1;
        }
        elseif (isset($agg_dbexpr) && $agg_dbexpr !== '') {
            if (preg_match('/(sum|min|max|avg|stddev|count)\\(/i', $agg_dbexpr)) {   # )
                $is_key = 0;
            }
            else {
                $is_key = 1;
            }
        }
        elseif ($column_type == null || preg_match('/_id$/',$column)) {
            $is_key = 1;
        }
        elseif ($column_type != 'integer' && $column_type != 'float') {
            $is_key = 1;
        }
        elseif ($column_type === 'integer' && preg_match('/_id$/',$column)) {
            $is_key = 1;
        }
        else {
            $is_key = 0;
        }
        return($is_key);
    }

    public function get_table_defs ($schema_name = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();

        if (!isset($schema_name)) $schema_name = $this->schema_name;

        if (!isset($this->table_defs[$schema_name]) || !isset($this->table_defs_loaded[$schema_name])) {     # If no table defs are loaded for this schema...
             $this->_load_table_defs($schema_name);        # ... then load them and ensure that they loaded
             if (!isset($this->table_defs[$schema_name])) throw new \Exception("get_table_defs($schema_name): schema is unknown or does not exist [".__LINE__."]");
        }

        $table_defs = $this->table_defs[$schema_name];

        if ($trace) trace_exit($table_defs);
        return($table_defs);
    }

    # I made a mistake doing both of these. I should have stuck with get_table_defs() alone.
    public function get_table_defs_2 ($schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        $table_defs = $this->get_table_defs($schema_name);
        if ($trace) trace_exit($table_defs);
        return($table_defs);
    }

    public function get_view_defs ($table_name, $schema_name = null, $options = null) {
        global $trace;
        if ($trace) trace_entry();

        ########################################################################################
        # parse the optional schema component out of the $table
        ########################################################################################
        if (preg_match('/^([a-zA-Z0-9_]+)\\.([a-zA-Z0-9_]+)$/', $table_name, $matches)) {
            $schema_name    = $matches[1];
            $fulltable_name = $table_name;
            $table_name     = $matches[2];
        }
        else {
            if (!isset($schema_name)) $schema_name = $this->schema_name;
            $fulltable_name = "$schema_name.$table_name";
        }

        $metadb = $this->metadb();

        $sql = <<<EOF
select
    v.view_key as view_name,
    v.view_label as view_label,
    count(distinct ch.chart_id) as num_children
from {meta_schema_}db_view v
     left join {meta_schema_}db_chart ch on ch.chart_key like concat(v.view_key,'.%') and ch.db_owner_group_id = ?
where v.db_owner_group_id = ?
  and v.view_key like ?
group by v.view_label, v.view_key
order by v.view_label, v.view_key
EOF;
        $view_defs = $metadb->get_hashes($sql, array($this->db_owner_group_id, $this->db_owner_group_id, "$this->db_name.$table_name.%"));

        if ($trace) trace_exit($view_defs);
        return($view_defs);
    }

    public function get_chart_defs ($view, $deep = 0) {
        global $trace;
        if ($trace) trace_entry();

        $metadb = $this->metadb();
        $chart_defs = $metadb->get_hashes("select ch.chart_key as chart_name, ch.chart_label as chart_label, data from {meta_schema_}db_chart ch where ch.db_owner_group_id = ? and ch.chart_key like ?", array($this->db_owner_group_id, "$view.%"));

        foreach ($chart_defs as $i => $chart_def) {
            if (!isset($chart_def['chart_label']) || !$chart_def['chart_label'] || $deep) {
                $data = json_decode($chart_def['data'], true);
                #if (!$data) {
                #    throw new \Exception("ERROR: json decode encountered error (".json_last_error().")");
                #}
            }
            if (!isset($chart_def['chart_label'])) {
                $chart_name = $chart_def['chart_name'];
                if (isset($data) &&
                    isset($data['title']) &&
                          $data['title']) {
                    $chart_defs[$i]['chart_label'] = $data['title'];
                }
                elseif (isset($data) &&
                        isset($data['chartConfig']) &&
                        isset($data['chartConfig']['title']) &&
                        isset($data['chartConfig']['title']['text']) &&
                              $data['chartConfig']['title']['text']) {
                    $chart_defs[$i]['chart_label'] = $data['chartConfig']['title']['text'];
                }
                elseif (preg_match("/^$view\\.(.+)$/", $chart_name, $matches)) {
                    $chart_defs[$i]['chart_label'] = $matches[1];
                }
                else {
                    $chart_defs[$i]['chart_label'] = $chart_name;
                }
            }
            if ($deep) {
                $chart_defs[$i]['data'] = $data;
            }
            else {
                unset($chart_defs[$i]['data']);
            }
        }

        if ($trace) trace_exit($chart_defs);
        return($chart_defs);
    }

    public function get_table_names ($schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name)) $schema_name = $this->schema_name;
        if (!isset($this->table_defs[$schema_name])) $this->_load_table_defs($schema_name);
        if ($trace) trace_exit(array_keys($this->table_defs[$schema_name]));
        return(array_keys($this->table_defs[$schema_name]));
    }

    public function get_primary_key_column ($table_name, $table_def = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($table_def)) $table_def = $this->get_table_def($table_name);
        if (isset($table_def['primary_key'])) {
            $num_columns = count($table_def['primary_key']);
            if ($num_columns == 1) {
                $primary_key_column = $table_def['primary_key'][0];
            }
            else {
                throw new \Exception("ERROR: get_primary_key_column($table_name) fails because primary_key does not have a single column ($num_columns columns)");
            }
        }
        else {
            throw new \Exception("ERROR: get_primary_key_column($table_name) fails because primary_key unknown");
        }
        if ($trace) trace_exit($primary_key_column);
        return($primary_key_column);
    }

    private function _clear_table_def ($schema_name, $table_name) {
        global $trace;
        if ($trace) trace_entry();
        if (isset($this->table_defs[$schema_name]) && isset($this->table_defs[$schema_name][$table_name])) {
            unset($this->table_defs[$schema_name][$table_name]);
        }
        if ($trace) trace_exit();
    }

    private function _load_table_def ($schema_name, $table_name) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name)) $schema_name = $this->schema_name;
        if (!isset($this->table_defs[$schema_name])) {
            $this->table_defs[$schema_name] = array();
        }
        if (!isset($this->table_defs[$schema_name][$table_name])) {
            $metadb     = $this->metadb();
            $schema_def = $this->get_schema_def($schema_name);
            $schema_id  = $schema_def['schema_id'];
            $table_def  = $metadb->get_hash("select * from {meta_schema_}db_table where schema_id = ? and table_name = ?", array($schema_id, $table_name));
            if (isset($table_def)) {
                $this->table_defs[$schema_name][$table_name] = $table_def;
            }
        }
        if ($trace) trace_exit();
    }

    private function _load_table_defs ($schema_name = null) {
        global $trace, $context;
        if ($trace) trace_entry();
        if (!isset($schema_name)) $schema_name = $this->schema_name;
        # Load the metadata for all instances from metadb
        if (!isset($this->table_defs_loaded[$schema_name])) {
            $db_name     = $this->db_name;
            $metadb      = $this->metadb();
            $schema_def  = $this->get_schema_def($schema_name);
            $schema_id   = $schema_def['schema_id'];
            $db_group_id = $this->db_owner_group_id;
            $sql = <<<EOF
select
    t.schema_id,
    t.table_id,
    t.table_name,
    t.table_alias,
    t.table_label,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size,
    t.physical_ind,
    t.db_owner_group_id,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size,
    count(distinct v.view_id) as num_children
from {meta_schema_}db_table t
     left join {meta_schema_}db_view v on v.view_key like concat('$db_name.',t.table_name,'.%') and v.db_owner_group_id = ?
where t.schema_id = ?
  and t.db_owner_group_id = ?
group by
    t.schema_id,
    t.table_id,
    t.table_name,
    t.table_alias,
    t.table_label,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size,
    t.physical_ind,
    t.db_owner_group_id,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size
order by table_label, table_name
EOF;
            if (!isset($this->table_defs[$schema_name])) {
                $this->table_defs[$schema_name] = array();
            }
            $table_defs = $metadb->get_hashes($sql, array($db_group_id, $schema_id, $db_group_id));
            if (count($table_defs) == 0) {
                $this->refresh_table_defs($schema_name);
                if (!isset($this->table_defs[$schema_name])) {
                    $this->table_defs[$schema_name] = array();
                }
            }
            else {
                if (!isset($this->table_defs[$schema_name])) {
                    $this->table_defs[$schema_name] = array();
                }
                foreach ($table_defs as $table_def) {
                    $table_name = $table_def['table_name'];
                    if (!isset($this->table_defs[$schema_name][$table_name])) {
                        $this->table_defs[$schema_name][$table_name] = $table_def;
                    }
                }
            }
            $this->table_defs_loaded[$schema_name] = true;
        }
        if ($trace) trace_exit();
    }

    private function _load_table_def_details ($schema_name, $table_name, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $metadb      = $this->metadb();
        #$schema_def = $this->get_schema_def($schema_name);
        $table_def  =& $this->table_defs[$schema_name][$table_name];
        $aggregate = (isset($options) && isset($options['aggregate'])) ? $options['aggregate'] : 0;

        # Load the metadata for all instances from metadb
        if (!isset($table_def['column'])) {
            $where_agg = $aggregate ? "(physical_ind = 'Y' or agg_dbexpr is not null)" : "(physical_ind = 'Y' or dbexpr is not null)";
            $column_defs_from_metadb = $metadb->get_hash_of_hashes_by_key("select * from {meta_schema_}db_column where table_id = $table_def[table_id] and $where_agg order by order_idx", null, null, 'column_name');
            $phys_columns = array();
            foreach ($column_defs_from_metadb as $column_name => $column_def) {
                if ($column_def['physical_ind'] === 'Y') {
                    $phys_columns[] = $column_name;
                }
                if ($column_def['primary_key_ind'] === 'Y') {
                    if (!isset($table_def['primary_key'])) $table_def['primary_key'] = array();
                    $table_def['primary_key'][] = $column_name;
                }
                if ($column_def['auto_increment_ind'] === 'Y') {
                    $table_def['auto_increment_column'] = $column_name;
                }
            }
            $table_def['phys_columns'] = $phys_columns;
            if ($aggregate) $this->_enhance_column_defs($column_defs_from_metadb, $options);
            $table_def['column']       = $column_defs_from_metadb;

            $index_defs_list = $metadb->get_hashes("select index_id, table_id, index_name, order_idx, primary_key_ind, auto_increment_ind, unique_ind, index_option from {meta_schema_}db_index where table_id = $table_def[table_id] order by order_idx");
            $index_defs = array();
            foreach ($index_defs_list as $i => $index_def) {
                $index_id = $index_def['index_id'];
                $index_defs_list[$i]['columns'] = array();
                $index_defs[$index_id] =& $index_defs_list[$i];
            }

            $index_columns          = $metadb->get_hashes("select i.index_id, i.unique_ind, i.primary_key_ind, i.auto_increment_ind, ci.asc_ind, c.column_name from {meta_schema_}db_index i inner join {meta_schema_}db_index_column ci on ci.index_id = i.index_id inner join {meta_schema_}db_column c on c.column_id = ci.column_id where i.table_id = $table_def[table_id] order by i.order_idx, ci.order_idx");

            $relationships          = $metadb->get_hash_of_hashes_by_key("select r.relationship_alias, r.relationship_label, r.order_idx, r.to_table, r.join_on_clause, r.dependencies, r.to_zero_ind, r.to_many_ind from {meta_schema_}db_relationship r where r.table_name = '$table_name' order by r.order_idx, r.relationship_alias", null, null, 'relationship_alias');
            $table_def['relationship'] =& $relationships;

            $relationship_aliases   = array();
            foreach ($relationships as $relationship_alias => $relationship) {
                $relationship_aliases[] = $relationship_alias;
            }
            $table_def['relationships'] =& $relationship_aliases;

            $unique_indexes = array();
            $indexes        = array();
            $index          = array();

            $table_def['unique_indexes'] =& $unique_indexes;
            $table_def['indexes']        =& $indexes;
            $table_def['index_defs']     =& $index_defs_list;

            $last_index_id = 0;
            foreach ($index_columns as $index_column) {
                $index_id = $index_column['index_id'];
                if ($last_index_id && $index_id != $last_index_id) {
                    if ($index_defs[$last_index_id]['primary_key_ind'] === 'Y') {
                        $table_def['primary_key'] = $index;
                    }
                    if ($index_defs[$last_index_id]['unique_ind'] === 'Y') {
                        $unique_indexes[] = $index;
                    }
                    else {
                        $indexes[] = $index;
                    }
                    $index = array();
                }
                $index[] = $index_column['column_name'];
                $index_defs[$index_id]['columns'][] = $index_column['column_name'];
                $last_index_id = $index_id;
            }
            if ($last_index_id) {
                if ($index_defs[$last_index_id]['primary_key_ind'] === 'Y') {
                    $table_def['primary_key'] = $index;
                }
                if ($index_defs[$last_index_id]['unique_ind'] === 'Y') {
                    $unique_indexes[] = $index;
                }
                else {
                    $indexes[] = $index;
                }
            }
            if (!isset($table_def['primary_key']) && count($table_def['unique_indexes']) > 0) {
                $table_def['primary_key'] = $table_def['unique_indexes'][0];
            }
        }
        if ($trace) trace_exit();
    }

    protected function _enhance_column_defs (&$column_defs, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $aggregate = (isset($options) && isset($options['aggregate'])) ? $options['aggregate'] : 0;
        if ($aggregate) {
            if (!isset($column_defs['count'])) {
                $column_defs['count'] = array('column_name' => 'count', 'column_label' => 'Count', 'column_type' => 'integer', 'not_null_ind' => 'Y',
                                              'physical_ind' => 'N', 'agg_dbexpr' => 'count(1)', 'dbexpr'=>'1');
            }
        }
        if ($trace) trace_exit();
    }

    protected function _enhance_columns (&$columns, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $aggregate = (isset($options) && isset($options['aggregate'])) ? $options['aggregate'] : 0;
        if ($aggregate) {
            if (!in_array('count', $columns)) {
                $columns[] = 'count';
            }
        }
        if ($trace) trace_exit();
    }

    protected function _load_extra_column_defs ($schema_name, $table_name, $relationships = null, $columns = null) {
        global $trace, $options;
        if ($trace) trace_entry();
        $metadb        = $this->metadb();
        $table_def    =& $this->table_defs[$schema_name][$table_name];
        if (!isset($table_def['column'])) $table_def['column'] = array();
        $column_defs  =& $table_def['column'];
        $where_relationship = isset($relationships) ? ("\n  and r.relationship_alias in ('" . implode("','", $relationships) . "')") : "";
        $where_column       = isset($columns)       ? ("\n  and c.column_name in ('" . implode("','", $columns) . "')") : "";
        $where_schema       = isset($options['schema_prefix']) ? "\n  and s.schema_name like '$options[schema_prefix]%'" : '';
        $sql = <<<EOF
select
    c.column_name,
    s.schema_name,
    c.column_alias,
    c.column_label,
    c.column_type,
    c.order_idx,
    c.max_length,
    c.not_null_ind,
    c.default_value,
    'N' as physical_ind,
    c.primary_key_ind,
    c.est_data_maxchars,
    (case when c.dbexpr is not null and c.dbexpr != '' then c.dbexpr else concat(r.relationship_alias,'.',c.column_name) end) as dbexpr,
    c.display_format,
    c.display_width,
    'N' as writeable_ind,
    r.relationship_alias
from {meta_schema_}db_relationship r
     inner join {meta_schema_}db_table t on t.table_name = r.to_table
     inner join {meta_schema_}db_column c on c.table_id = t.table_id
     inner join {meta_schema_}db_schema s on s.schema_id = t.schema_id
where r.table_name = '$table_name'$where_relationship$where_column$where_schema
  and s.db_connection_id = $this->db_connection_id          -- temporary: until we get cross-db joins working
order by
    c.column_name,
    (case when s.db_connection_id = $this->db_connection_id then 1 else 2 end),
    (case when s.schema_name = '$schema_name' then 1 else 2 end),
    r.order_idx,
    r.relationship_alias
EOF;
        $extra_column_defs = $metadb->get_hashes($sql);
        foreach ($extra_column_defs as $extra_column_def) {
            $column_name = $extra_column_def['column_name'];
            if (!isset($column_defs[$column_name])) {
                $column_defs[$column_name] = $extra_column_def;
            }
        }

        if ($trace) trace_exit();
    }

    public function get_column_names ($table_name, $schema_name = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($schema_name)) $schema_name = $this->schema_name;
        $table_def = $this->get_table_def($table_name, $schema_name);
        if ($trace) trace_exit($table_def['column_names']);
        return($table_def['column_names']);
    }

    public function make_table_alias ($table_name, &$alias_used = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($alias_used)) {
            $alias_used = array();
        }
        if (count($alias_used) == 0) {
            $table_defs = $this->get_table_defs();
            foreach ($table_defs as $table_name => $table_def) {
                $alias_used[$table_def['table_alias']] = 1;
            }
        }
        $table_alias = $this->make_alias($table_name, $alias_used);
        if ($trace) trace_exit($table_alias);
        return($table_alias);
    }

    public function make_column_alias ($table_name, $column_name, &$alias_used = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($alias_used)) {
            $alias_used = array();
        }
        if (count($alias_used) == 0) {
            $table_def = $this->get_table_def($table_name);
            foreach ($table_def['column'] as $column_name => $column_def) {
                $alias_used[$column_def['column_alias']] = 1;
            }
        }
        $column_alias = $this->make_alias($column_name, $alias_used);
        if ($trace) trace_exit($column_alias);
        return($column_alias);
    }

    public function make_alias ($symbol, &$alias_used = null) {
        $alias = "";
        if (preg_match('/_/', $symbol)) {
            $symparts = preg_split('/_/', $symbol);
            for ($i = 0; $i < sizeof($symparts); $i++) {
                $alias .= substr($symparts[$i], 0, 1);
            }
        }
        else {
            if (preg_match('/^...[AEIOUaeiou]/', $symbol)) {
                $alias = substr($symbol, 0, 3);
            }
            else {
                $alias = substr($symbol, 0, 4);
            }
        }
        if (isset($alias_used)) {
            if (isset(self::$reserved_word[$alias]) || isset($alias_used[$alias])) {
                $i = 2;
                $try_alias = $alias . $i;
                while (isset(self::$reserved_word[$try_alias]) || isset($alias_used[$try_alias])) {
                    $i++;
                    $try_alias = $alias . $i;
                }
                $alias = $try_alias;
            }
            $alias_used[$alias] = 1;
        }
        else {
            if (isset(self::$reserved_word[$alias])) {
                $i = 2;
                $try_alias = $alias . $i;
                while (isset(self::$reserved_word[$try_alias])) {
                    $i++;
                    $try_alias = $alias . $i;
                }
                $alias = $try_alias;
            }
        }
        return($alias);
    }

    public function make_label ($symbol) {
        $label = preg_replace('/_/',' ',strtolower($symbol));
        $label = ucwords($label);
        return($label);
    }

    public function init_table_defs ($schema_name = null, $table_name = null, $verbose = 0) {
        global $trace;
        if ($trace) trace_entry();
        $this->_load_schema_defs($schema_name);
        if ($verbose) echo ">> schema names ...\n";

        if ($schema_name) $schema_names = array($schema_name);
        else         $schema_names = $this->get_schema_names();
        if ($verbose) echo ">> schema names [$this->name]: [", implode(", ", $schema_names), "]\n";

        foreach ($schema_names as $schema_name) {
            if ($verbose) echo ">> initializing schema [$schema_name] metadata ...\n";
            $this->_load_table_defs($schema_name);
            if ($table_name) $table_names = array($table_name);
            else        $table_names = $this->get_table_names($schema_name);
            if ($verbose) echo ">> table names [$schema_name]: [", implode(", ", $table_names), "]\n";
            foreach ($table_names as $table_name) {
                if ($verbose) echo ">> >> initializing table [$table_name] metadata ...\n";
                $this->init_table_def($schema_name, $table_name);
                $table_def = $this->get_table_def($table_name, $schema_name);
            }
        }
        if ($trace) trace_exit();
    }

    public function init_table_def ($schema_name, $table_name) {
        global $trace;
        if ($trace) trace_entry();
        $this->_load_schema_defs($schema_name);
        $table_def = $this->refresh_table_metadata($schema_name, $table_name);
        if ($trace) trace_exit();
    }

    public function refresh_table_defs ($schema_name) {
        global $trace, $context;
        if ($trace) trace_entry();
        $schema_def = $this->get_schema_def($schema_name);
        $schema_id = $schema_def['schema_id'];
        $db_group_id = $this->db_owner_group_id;
        $table_defs_from_db = $this->_get_table_defs_from_db($schema_name);
        if (count($table_defs_from_db) > 0) {
            $db_name = $this->db_name;
            $metadb = $this->metadb();
            $sql = <<<EOF
select
    t.schema_id,
    t.table_id,
    t.table_name,
    t.table_alias,
    t.table_label,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size,
    t.physical_ind,
    t.db_owner_group_id,
    count(distinct v.view_id) as num_children
from {meta_schema_}db_table t
     left join {meta_schema_}db_view v on v.view_key like concat('$db_name.',t.table_name,'.%') and v.db_owner_group_id = ?
where t.schema_id = ?
  and t.table_name = ?
  and t.db_owner_group_id = ?
group by
    t.schema_id,
    t.table_id,
    t.table_name,
    t.table_alias,
    t.table_label,
    t.est_row_count,
    t.est_data_size,
    t.est_index_size,
    t.physical_ind,
    t.db_owner_group_id
EOF;
            $table_alias_used  = array();
            foreach ($table_defs_from_db as $table_name => $table_def_from_db) {
                $table_def = $metadb->get_hash($sql, array($db_group_id, $schema_id, $table_name, $db_group_id));
                if (isset($table_def)) {
                    $this->table_defs[$schema_name][$table_name] = $table_def;
                }
                else {
                    $table_alias = $this->make_alias($table_name, $alias_used);
                    $table_label = $this->make_label($table_name);
                    $table_id = $metadb->get("select table_id from {meta_schema_}db_table where schema_id = ? and table_name = ?",
                        array($schema_id, $table_name));
                    if (!isset($table_id)) {
                        $metadb->execute("insert into {meta_schema_}db_table (schema_id, table_name, table_alias, table_label, est_row_count, est_data_size, est_index_size, physical_ind, db_owner_group_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
                            array($schema_id, $table_name, $table_alias, $table_label, null, null, null, 'Y', $db_group_id));
                        $table_id = $metadb->last_insert_id('db_table');
                    }

                    $this->table_defs[$schema_name][$table_name] = array(
                        'schema_id' => $schema_id,
                        'table_id' => $table_id,
                        'table_name' => $table_name,
                        'table_alias' => $table_alias,
                        'table_label' => $table_label,
                        'est_row_count' => null,
                        'est_data_size' => null,
                        'est_index_size' => null,
                        'physical_ind' => 'Y',
                        'db_owner_group_id' => $db_group_id,
                        'num_children' => 0
                    );
                }
            }
        }
        else {
            $this->table_defs[$schema_name] = array();
        }
        if ($trace) trace_exit();
    }

    public function refresh_table_metadata ($schema_name, $table_name, $table_def_from_db = null) {
        global $trace;
        if ($trace) trace_entry();
        if (!isset($table_def_from_db)) $table_def_from_db = $this->get_table_def_from_db($table_name, $this->schema_name);
        $table_id = null;
        $raw = array('raw'=>1);
        $table_metadata_exists = 0;
        $db_group_id = $this->db_owner_group_id;
        try {
            $table_def =& $this->get_table_def($table_name, $schema_name);
            $table_id = $table_def['table_id'];
            $table_metadata_exists = 1;
        } catch (\Exception $e) {
debug_print("XXXXX: ".$e->getMessage());
            $table_metadata_exists = 0;
            # PHYSICAL TABLE DOES NOT EXIST, AND IT DOES NOT EXIST IN "META" => DO NOTHING
            if (!isset($table_def_from_db)) {
                # do nothing
            }
            # PHYSICAL TABLE EXISTS, AND IT DOES NOT EXIST IN "META" => CREATE FROM SCRATCH
            else {
                $table_alias_used  = array();
                $column_alias_used = array();
                $schema_def  = $this->get_schema_def($schema_name);
                $schema_id   = $schema_def['schema_id'];
                $table_alias = $this->make_table_alias($table_name, $table_alias_used);
                $table_label = $this->make_label($table_name);
                $metadb      = $this->metadb();
                $nrows = $metadb->execute("insert into {meta_schema_}db_table (schema_id, table_name, table_alias, table_label, est_row_count, est_data_size, est_index_size, physical_ind, db_owner_group_id) values (?, ?, ?, ?, ?, ?, ?, ?, ?) on duplicate key update table_alias = values(table_alias), est_row_count = values(est_row_count), est_data_size = values(est_data_size), est_index_size = values(est_index_size), physical_ind = values(physical_ind), db_owner_group_id = values(db_owner_group_id)",
                    array($schema_id, $table_name, $table_alias, $table_label,
                          $table_def_from_db['est_row_count'], $table_def_from_db['est_data_size'], $table_def_from_db['est_index_size'], 'Y', $db_group_id));
                if ($nrows == 2) {
                    $table_id = $metadb->get("select table_id from {meta_schema_}db_table where schema_id = ? and table_name = ?", array($schema_id, $table_name));
                }
                else {
                    $table_id = $metadb->last_insert_id('db_table');
                }
                $this->table_defs[$schema_name][$table_name] = $metadb->get_hash("select * from {meta_schema_}db_table where table_id = ?", array($table_id));

                # db_column
                $phys_columns_from_db = $table_def_from_db['phys_columns'];
                $db_column_insert_columns = array('table_id', 'physical_ind', 'column_name', 'column_type', 'column_label', 'column_alias', 'order_idx',
                                                  'default_value', 'not_null_ind', 'max_length', 'primary_key_ind', 'auto_increment_ind', 'db_owner_group_id');

                foreach ($phys_columns_from_db as $i => $column_name) {
                    $column_def_from_db = $table_def_from_db['column'][$column_name];
                    $column_def_from_db['table_id'] = $table_id;
                    if (!isset($column_def_from_db['column_label'])) {
                        $column_def_from_db['column_label'] = $this->make_label($column_name);
                    }
                    if (!isset($column_def_from_db['column_alias'])) {
                        $column_def_from_db['column_alias'] = $this->make_column_alias($table_name, $column_name, $column_alias_used);
                    }
                    $column_def_from_db['db_owner_group_id'] = $db_group_id;
                    $column_id = $metadb->get("select c.column_id from {meta_schema_}db_column c where c.table_id = ? and c.column_name = ?",
                        array($table_id, $column_name));
                    if (!isset($column_id)) {
                        $metadb->insert('db_column', $db_column_insert_columns, $column_def_from_db, $raw);
                        $column_def_from_db['column_id'] = $metadb->last_insert_id('db_column');
                    }
                }
                $this->_clear_table_def($schema_name, $table_name);
                $table_def =& $this->get_table_def($table_name, $schema_name);
                $table_metadata_exists = 1;
            }
        }
        if ($table_metadata_exists) {
            # PHYSICAL TABLE DOES NOT EXIST, BUT IT MISTAKENLY EXISTS IN "META" => DROP METADATA
            if (!isset($table_def_from_db)) {
                $this->drop_table_metadata($schema_name, $table_name);
            }
            # PHYSICAL TABLE EXISTS, AND IT EXISTS IN "META" => UPDATE METADATA
            else {
                $metadb     = $this->metadb();
                $column_alias_used = array();
                $table_id = $table_def['table_id'];
                $metadb->update('db_table',
                                array('table_id' => $table_id),
                                array('table_name','est_row_count','est_data_size','est_index_size','physical_ind'),
                                $table_def_from_db);
                # db_column
                $phys_columns_from_db = $table_def_from_db['phys_columns'];
                $phys_columns         = $table_def['phys_columns'];
                $columns              = $table_def['columns'];

                $db_column_insert_columns = array('table_id', 'physical_ind', 'column_name', 'column_type', 'column_label', 'column_alias', 'order_idx',
                                                  'default_value', 'not_null_ind', 'max_length', 'primary_key_ind', 'auto_increment_ind', 'db_owner_group_id');

                $db_column_update_columns = array('physical_ind', 'column_name', 'column_type', 'order_idx', 'default_value', 'not_null_ind', 'max_length',
                                                  'primary_key_ind', 'auto_increment_ind');

                foreach ($phys_columns_from_db as $i => $column_name) {
                    $column_def_from_db = $table_def_from_db['column'][$column_name];
                    if (in_array($column_name, $phys_columns)) {
                        $column_def     = $table_def['column'][$column_name];
                        $column_id      = $column_def['column_id'];
                        $metadb->update('db_column', array('column_id' => $column_id), $db_column_update_columns, $column_def_from_db, $raw);

                        # update the in-memory version of the table_def
                        foreach ($db_column_update_columns as $col) {
                            $table_def['column'][$column_name][$col] = $column_def_from_db[$col];
                        }
                    }
                    else {
                        # ???: "user_mode": "7", "group_mode": "7", "other_mode": "0", "db_owner_group_id": "1",
                        $column_def_from_db['table_id'] = $table_id;
                        if (!isset($column_def_from_db['column_label'])) {
                            $column_def_from_db['column_label'] = $this->make_label($column_def_from_db['column_name']);
                        }
                        if (!isset($column_def_from_db['column_alias'])) {
                            $column_def_from_db['column_alias'] = $this->make_column_alias($table_name, $column_name, $column_alias_used);
                        }
                        $column_def_from_db['db_owner_group_id'] = $db_group_id;
                        $metadb->insert('db_column', $db_column_insert_columns, $column_def_from_db, $raw);
                        $column_def_from_db['column_id'] = $metadb->last_insert_id('db_column');

                        # update the in-memory version of the table_def
                        $table_def['phys_columns'][] = $column_name;
                        $table_def['columns'][] = $column_name;
                        $table_def['column'][$column_name] = $column_def_from_db;
                    }
                }
                foreach ($phys_columns as $i => $column_name) {
                    $column_def     = $table_def['column'][$column_name];
                    if (!in_array($column_name, $phys_columns_from_db)) {
                        $column_id  = $column_def['column_id'];
                        $metadb->delete('db_column', array('column_id' => $column_id), $raw);

                        # update the in-memory version of the table_def
                        unset($table_def['column'][$column_name]);
                        $i = array_search($column_name, $table_def['columns']);
                        if ($i !== false) array_splice($table_def['columns'], $i, 1);
                        $i = array_search($column_name, $table_def['phys_columns']);
                        if ($i !== false) array_splice($table_def['phys_columns'], $i, 1);
                    }
                }

                # db_index
                $db_index_select_columns = array('index_id', 'table_id', 'index_name', 'order_idx', 'primary_key_ind', 'auto_increment_ind', 'unique_ind',
                                                 'index_option', 'db_owner_group_id');
                $indexes = $metadb->get_hash_of_hashes_by_key('db_index', array('table_id'=>$table_id), $db_index_select_columns, 'index_name', $raw);

                $db_index_column_select_sql = "select ic.index_column_id, ic.index_id, ic.column_id, ic.order_idx, ic.asc_ind, ic.db_owner_group_id, c.column_name from {meta_schema_}db_index_column ic left join {meta_schema_}db_column c on c.column_id = ic.column_id where ic.index_id = ? order by ic.order_idx";

                #"unique_indexes": [],
                #"indexes": [],
                #"index_defs": [
                #    {
                #        "index_name": "PRIMARY",
                #        "primary_key_ind": "Y",
                #        "unique_ind": "Y",
                #        "auto_increment_ind": "Y",
                #        "order_idx": 1,
                #        "columns": [
                #            "stephen_id"
                #        ]
                #    }
                #]

                # MODIFY OR ADD METADATA FOR EXISTING INDEXES
                foreach ($table_def_from_db['index_defs'] as $index_def_from_db) {
                    $index_name = $index_def_from_db['index_name'];
                    # INDEX IS A PRIMARY KEY. IGNORE IT.
                    if (isset($index_def_from_db['primary_key_ind']) && $index_def_from_db['primary_key_ind'] === 'Y') {
                        # do nothing
                    }
                    else {
                        # INDEX EXISTS IN THE METADATA
                        if (isset($indexes[$index_name])) {
                            $index_def = $indexes[$index_name];
                            $index_id  = $index_def['index_id'];

                            if ($index_def['primary_key_ind']    !== $index_def_from_db['primary_key_ind'] ||
                                $index_def['unique_ind']         !== $index_def_from_db['unique_ind'] ||
                                $index_def['auto_increment_ind'] !== $index_def_from_db['auto_increment_ind'] ||
                                $index_def['order_idx']          !== $index_def_from_db['order_idx']) {
                                $metadb->execute(
                                    "update {meta_schema_}db_index set primary_key_ind = ?, unique_ind = ?, auto_increment_ind = ?, order_idx = ? where index_id = ?",
                                    array($index_def_from_db['primary_key_ind'],
                                          $index_def_from_db['unique_ind'],
                                          $index_def_from_db['auto_increment_ind'],
                                          $index_def_from_db['order_idx'],
                                          $index_id));
                            }

                            $index_column_defs = $metadb->get_hashes($db_index_column_select_sql, array($index_id));
                            $index_columns = array();
                            foreach ($index_column_defs as $index_column_def) {
                                $index_columns[] = $index_column_def['column_name'];
                            }
                            $index_column_list = implode(',', $index_columns);
                            $index_column_list_from_db = implode(',', $index_def_from_db['columns']);
                            if ($index_column_list !== $index_column_list_from_db) {
                                $metadb->execute("delete from {meta_schema_}db_index_column where index_id = ?", array($index_id));
                                foreach ($index_def_from_db['columns'] as $i => $column_name) {
                                    $column_id = $table_def['column'][$column_name]['column_id'];
                                    $metadb->execute("insert into {meta_schema_}db_index_column (index_id, column_id, order_idx, asc_ind, db_owner_group_id) values (?, ?, ?, ?, ?)",
                                               array($index_id, $column_id, $i+1, 'Y', $db_group_id));
                                }
                                if (preg_match("/^{$table_name}_(ie|ak)([0-9]+)$/", $index_name, $matches)) {
                                    $index_idx = $matches[2] - 1;
                                    if ($matches[1] === 'ie') {
                                        $table_def['indexes'][$index_idx] = $index_columns;
                                    }
                                    elseif ($matches[1] === 'ak') {
                                        $table_def['unique_indexes'][$index_idx] = $index_columns;
                                    }
                                }
                            }
                        }
                        # INDEX DOES NOT EXIST IN THE METADATA
                        else {
                            $metadb->execute(
                                "insert into {meta_schema_}db_index (table_id, index_name, primary_key_ind, unique_ind, auto_increment_ind, order_idx) values (?, ?, ?, ?, ?, ?)",
                                array($table_id,
                                      $index_name,
                                      $index_def_from_db['primary_key_ind'],
                                      $index_def_from_db['unique_ind'],
                                      $index_def_from_db['auto_increment_ind'],
                                      $index_def_from_db['order_idx']));
                            $index_id = $metadb->last_insert_id('db_index');

                            foreach ($index_def_from_db['columns'] as $i => $column_name) {
                                $column_id = $table_def['column'][$column_name]['column_id'];
                                $metadb->execute("insert into {meta_schema_}db_index_column (index_id, column_id, order_idx, asc_ind, db_owner_group_id) values (?, ?, ?, ?, ?)",
                                           array($index_id, $column_id, $i+1, 'Y', $db_group_id));
                            }
                            if (preg_match("/^{$table_name}_(ie|ak)([0-9]+)$/", $index_name, $matches)) {
                                $index_idx = $matches[2] - 1;
                                if ($matches[1] === 'ie') {
                                    $table_def['indexes'][$index_idx] = $index_def_from_db['columns'];
                                }
                                elseif ($matches[1] === 'ak') {
                                    $table_def['unique_indexes'][$index_idx] = $index_def_from_db['columns'];
                                }
                            }
                        }
                    }
                }
            }
        }
        if ($trace) trace_exit();
    }

    public function rename_table_metadata ($schema_name, $table_name, $prev_table_name) {
        global $trace;
        if ($trace) trace_entry();
        $table_def =& $this->get_table_def($prev_table_name, $schema_name);
        $metadb = $this->metadb();
        $metadb->execute("update {meta_schema_}db_table set table_name = ? where table_id = ?", array($table_name, $table_def['table_id']));
        $table_def['table_name'] = $table_name;
        $this->table_defs[$schema_name][$table_name] = $table_def;
        $this->_clear_table_def($schema_name, $table_name);
        if ($trace) trace_exit();
    }

    public function drop_table_metadata ($schema_name, $table_name) {
        global $trace;
        if ($trace) trace_entry();
        $table_def =& $this->get_table_def($table_name, $schema_name);
        $metadb = $this->metadb();
        $table_id = $table_def['table_id'];
        $index_defs = $table_def['index_defs'];
        foreach($index_defs as $index_def) {
            $index_id = $index_def['index_id'];
            $metadb->execute("delete from {meta_schema_}db_index_column where index_id = ?", array($index_id));
        }
        $metadb->execute("delete from {meta_schema_}db_index where table_id = ?", array($table_id));
        $metadb->execute("delete from {meta_schema_}db_column where table_id = ?", array($table_id));
        $metadb->execute("delete from {meta_schema_}db_table where table_id = ?", array($table_id));
        $this->_clear_table_def($schema_name, $table_name);
        if ($trace) trace_exit();
    }

    public function get_row ($table_name, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $row = null;
        if (isset($options)) { $options['limit'] = 1; }
        else                 { $options = Array('limit' => 1); }
        $rows = $this->get_rows($table_name, $params, $columns, $options);
        if (count($rows) > 0) $row = $rows[0];
        if ($trace) trace_exit($row);
        return($row);
    }

    public function get_hash ($table_name, $params = null, $columns = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $row = null;
        if (isset($options)) { $options['limit'] = 1; }
        else                 { $options = Array('limit' => 1); }
        $rows = $this->get_hashes($table_name, $params, $columns, $options);
        if (count($rows) > 0) $row = $rows[0];
        if ($trace) trace_exit($row);
        return($row);
    }

    public function get ($table_name, $params = null, $column = null, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $value = null;
        if ($column == null) $row = $this->get_row($table_name, $params, null,           $options);
        else                 $row = $this->get_row($table_name, $params, Array($column), $options);
        if (isset($row)) $value = $row[0];
        if ($trace) trace_exit($value);
        return($value);
    }

    public function get_hash_of_values_by_key ($table_name, $params = null, $column = null, $key_column, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $hash = array();
        if ($column == null) $rows = $this->get_rows($table_name, $params, null,                        $options);
        else                 $rows = $this->get_rows($table_name, $params, Array($key_column, $column), $options);
        foreach ($rows as $row) {
            $hash[$row[0]] = $row[1];
        }
        if ($trace) trace_exit($hash);
        return($hash);
    }

    public function get_hash_of_hashes_by_key ($table_name, $params = null, $columns = null, $key_column, &$options = null) {
        global $trace;
        if ($trace) trace_entry();
        $hash = array();
        $rows = $this->get_hashes($table_name, $params, $columns, $options);
        foreach ($rows as $row) {
            if (!isset($row[$key_column]))
                throw new \Exception("ERROR: get_hash_of_hashes_by_key($table_name,...): $key_column must be a non-null value in the list of columns selected");
            $hash[$row[$key_column]] = $row;
        }
        if ($trace) trace_exit($hash);
        return($hash);
    }

    public function initializeDatabaseTables() {
        global $trace, $debug;
        if ($trace) trace_entry();
        $options = $this->context->options();
        # this stuff was for sqlite ...
        # if (!is_dir($this->db_directory)) $this->context->mkdir($this->db_directory);   // create directories recursively
        # touch($this->db_filename);
        # chmod($this->db_filename, 0664);
        if (isset($options['apps_dir'])) {
            $apps_dir = $options['apps_dir'];
            $schema_files = glob("$apps_dir/sitefront/front-framework/db/$this->dbtype/$this->name.*.sql");
            if ($debug) debug_print("initializeDatabase(): found schema files to initialize database with:\n" . print_r($schema_files,true));
            if (isset($schema_files)) {
                foreach ($schema_files as $schema_file) {
                    $this->run_file($schema_file);
                }
                #$this->init_table_defs();   # build the metadata
            }
        }
        if ($trace) trace_exit();
    }

    public function last_insert_id ($table_name) {
        throw new \Exception("last_insert_id(): not yet implemented");
    }

    protected function substitute_sql_vars ($sql) {
        global $trace;
        if ($trace) trace_entry();
        #debug_print('substitute_sql_vars(): ' . print_r($this->instance_sql_var,true) . print_r(static::$sql_var,true));
        $matches = array();
        while (preg_match('/\\{([a-zA-Z0-9.,_-]+)\\}/', $sql, $matches)) {
            $sql_tag = $matches[1];
            if (array_key_exists($sql_tag, $this->instance_sql_var)) {
                $sql = preg_replace("/\{$sql_tag\}/", $this->instance_sql_var[$sql_tag], $sql);
            }
            elseif (array_key_exists($sql_tag, static::$sql_var)) {
                $sql = preg_replace("/\{$sql_tag\}/", static::$sql_var[$sql_tag], $sql);
            }
            else break;
        }
        if ($trace) trace_exit($sql);
        return($sql);
    }

    ###################################################################################
    # These are the official "application" data types
    ###################################################################################
    # string
    # integer
    # float
    # date
    # datetime
    # time
    # text
    # blob

    protected function native_type_to_app_type ($native_data_type, $max_length = null, $number_of_decimals = null, $column_name = null) {
        global $trace;
        if ($trace) trace_entry();

        $native_data_type = strtolower($native_data_type);
        $matches = array();
        if (preg_match('/^([a-zA-Z]+)\\(([0-9]+),?([0-9]*)\\)/', $native_data_type, $matches)) {
            $native_data_type = $matches[1];
            if ($matches[2]) $max_length  = $matches[2] + 0;
            if ($matches[3]) $number_of_decimals = $matches[3] + 0;
        }

        if ($native_data_type == 'date' ||
            $native_data_type == 'datetime' ||
            $native_data_type == 'time') {
            $app_data_type = $native_data_type;
            # Some databases (Oracle) don't distinguish between dates and datetimes.
            # If a column_name is supplied, it provides the hint we are looking for to determine if it is a datetime.
            if ($app_data_type == 'date' && isset($column_name)) {
                $context_options = $this->context->options();
                $datetime_patterns = isset($context_options['datetime_patterns']) ?  $context_options['datetime_patterns'] : '_dttm$';
                $datetime_patterns = explode(',', $datetime_patterns);
                foreach ($datetime_patterns as $datetime_pattern) {
                    if (preg_match("/$datetime_pattern/", $column_name)) {
                        $app_data_type = 'datetime';
                        break;
                    }
                }
            }
        }
        elseif ($native_data_type == 'timestamp') {
            $app_data_type = 'datetime';
        }
        elseif (preg_match('/int|signed/', $native_data_type)) {
            $app_data_type = 'integer';
        }
        elseif (preg_match('/number|decimal/', $native_data_type) && isset($number_of_decimals) && $number_of_decimals <= 0) {
            $app_data_type = 'integer';
        }
        elseif (preg_match('/number|decimal|float|real|double/', $native_data_type)) {
            $app_data_type = 'float';
        }
        elseif (preg_match('/text|clob/', $native_data_type)) {
            $app_data_type = 'text';
        }
        elseif (preg_match('/blob|bin/', $native_data_type)) {
            $app_data_type = 'blob';
        }
        elseif (preg_match('/string/', $native_data_type)) {
            $app_data_type = 'string';
        }
        elseif (preg_match('/char|enum|set/', $native_data_type)) {
            $app_data_type = ($max_length == null || $max_length < 256) ? 'string' : 'text';
        }
        elseif (preg_match('/long|short/', $native_data_type)) {
            $app_data_type = 'integer';
        }
        else {
            $app_data_type = 'blob';
        }
        if ($trace) trace_exit($app_data_type);
        return $app_data_type;
    }

    protected function app_type_to_native_type ($app_data_type, $max_length = null, $number_of_decimals = null, $column_name = null) {
        global $trace;
        if ($trace) trace_entry();

        $app_data_type = isset($app_data_type) ? strtolower($app_data_type) : 'string';
        $matches = array();
        if (preg_match('/^([a-zA-Z]+)\\(([0-9]+),?([0-9]*)\\)/', $app_data_type, $matches)) {
            $app_data_type = $matches[1];
            if ($matches[2]) $max_length  = $matches[2] + 0;
            if ($matches[3]) $number_of_decimals = $matches[3] + 0;
        }

        if ($app_data_type == 'date' ||
            $app_data_type == 'datetime' ||
            $app_data_type == 'time') {
            $native_data_type = $app_data_type;
        }
        elseif (preg_match('/int|signed/', $app_data_type)) {
            $native_data_type = 'integer';
        }
        elseif (preg_match('/float/', $app_data_type)) {
            $native_data_type = 'float';
        }
        elseif (preg_match('/text/', $app_data_type)) {
            $native_data_type = 'text';
        }
        elseif (preg_match('/blob/', $app_data_type)) {
            $native_data_type = 'blob';
        }
        elseif (preg_match('/string/', $app_data_type)) {
            if (!isset($max_length) || !$max_length) $max_length = 255;
            $native_data_type = "varchar($max_length)";
        }
        else {
            $native_data_type = 'blob';
        }
        if ($trace) trace_exit($native_data_type);
        return $native_data_type;
    }

    public function assoc_as_string (&$array) {
        $str = '[';
        $first = 1;
        foreach ($array as $key => $value) {
            if (!$first) $str .= ', ';
            $str .= "$key=>$value";
            $first = 0;
        }
        $str .= ']';
        return $str;
    }

    public function is_assoc (&$array) {
        if (!array_key_exists(0, $array))
            return 1;
        $count = count($array);
        if (!array_key_exists($count-1, $array))
            return 1;
        return 0;
    }
}

